[펌] MSSQL 날짜 함수

http://flashcafe.org/sqlserver_study/4277/page/3

함수(날짜열)
DATE구분	구분약자	DATE구분	구분약자
year	yy	week	wk
quarter	qq	hour	hh
month	mm	minute	mi
day of year	dy	second	ss
day	dd	millisecond	ms
항목	내용
GETDATE()	시스템에서 제공하는 날짜와 시간 값
산술연산	-- date + NUMBER -- date - NUMBER

--날짜는 계산할 수 있다.
--getdate()
select getdate()'현재날짜 & 시간'
--getdate() -1, +1
select getdate() - 1'어제 이 시간',
   getdate() + 1'내일 이 시간'
--오늘부터 100일 전의 날짜를 검색!
select getdate() - 100
--회원테이블의 생일과 1200일째 되는 날을 검색! (Alias는 회원명, 생일, 12000일째)
select mem_name "회원명", mem_bir "생일",
   getdate() + 1200 "1200일째"
 from member

--**********************************************************************
--   DATEADD 함수(날짜열)
--**********************************************************************
--DATEADD (Part,n,date)/Part부분에 n만큼 더한 date
--      (DATE구분별로 모두 적용)   

select getdate() '현재날짜 & 시간',
   dateadd(year, 1, getdate()) '1년뒤'
--
select getdate() '현재날짜 & 시간',
   dateadd(mm,-1, getdate()) '한달전'
--
select getdate()'현재날짜 & 시간',
   dateadd(dd, -50, getdate()) '50일전'
--
select getdate()'현재날짜 & 시간',
   dateadd(hour, 7, getdate()) '7시간 뒤'
--
select dateadd(yy, 1, '19990228') "1년후",
   dateadd(yy, 2, '19990228') "2년후",
   dateadd(yy, 3, '19990228') "3년후"
--
select dateadd(mm, 12, '19000228') "1년후",
   dateadd(mm, 24, '19000228') "2년후",
   dateadd(mm, 36, '19000228') "3년후"
--오늘부터 6개월 전의 날짜를 검색!
select dateadd(mm, -6, getdate())"6개월전의 날짜"
--회원테이블에서 구길동회원의 생일과 12000일째 되는 날을 검색!
--(DATEADD사용) (Alias는 회원명, 생일, 12000일째)
--1.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
   where mem_name like '구길동%'
--2.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
   where mem_name like '구길동'
--3.
select mem_name"회원명", mem_bir"생일", dateadd(dd,1200,mem_bir)"1200일째" from member
   where mem_name='구길동'

--********************************************************************
--      YEAR,MONTH,DAY,DATEDIFF함수(날짜열)
--********************************************************************
--year, month, day 해당일의 년월일 값을 돌려준다.
select year(getdate())'year',
   month(getdate())'month',
   day(getdate())'day'
--datediff(part,date1,date2)/두 날짜 사이의 DATE구분 사이 값을 계산함.
--   (part, date1, date2)
select datediff(dd,'1999/02/15','2000/02/15')'day',
   datediff(mm,'1999/02/15','2000/02/15')'month',
   datediff(yy,'1999/02/15','2000/02/15')'year'
--
select datediff(dd,'1999/11/06','2000/02/15')'day',
   datediff(mm,'1999/11/06','2000/02/15')'month',
   datediff(yy,'1999/11/06','2000/02/15')'year'
--회원테이블의 생일과 오늘의 일수 차이를 검색! (Alias는 회원명, 생일, 오늘, 태어난지?)
select mem_name"회원명", mem_bir"생일",
   getdate()"오늘",
   datediff(dd,mem_bir,getdate())"태어난지?"
 from member
--본인의 생년월일과 오늘의 일수 차이를 비교 검색!
select datediff(dd,'1972/08/14',getdate()) "차이는"

--*******************************************************************
--      Datename, Datepart함수(날짜열)
--*******************************************************************
--datename(part,date)/date에서 datepart부분의 ASCII값(구분문자)
--datepart(part,date)/date에서 datepart부분의 정수값(구분숫자)
select convert(char,getdate()), '<==현재날짜'
select datename(yy,getdate()),datepart(yy,getdate()),'<==년도'
select datename(qq,getdate()),datepart(qq,getdate()),'<==분기'
select datename(mm,getdate()),datepart(mm,getdate()),'<==월'
select datename(dy,getdate()),datepart(dy,getdate()),'<==일수'
select datename(dd,getdate()),datepart(dd,getdate()),'<==해당일'
select datename(wk,getdate()),datepart(wk,getdate()),'<==주수'
select datename(dw,getdate()),datepart(dw,getdate()),'<==요일'
select datename(hh,getdate()),datepart(hh,getdate()),'<==시간'
--회원테이블에서 구길동회원의 생일의 DATENAME 과 DATEPART를 검색!
--(Alias 는 회원명, 생일, 기타 上 同)
select mem_name"회원명", mem_bir"생일",
   datename(dw,mem_bir)'기타上同',
   datepart(dw,mem_bir)'기타上同'
 from member
   where mem_name='구길동'
/* 월 (2002년 2월)만 입력받아 해당월의 시작일과 종료일을 검색!
Alias는 해당월, 시작일, 종료일) */
--******************** 1. 2002-02-01의 1달 후 -1....
select '200년02월' 해당월, '200-02-01' 시작일,
   dateadd(mm,1,'2000-02-01')-1 "종료일"
--******************** 2. 2002-03-01의 1일 전....
select '200년02월' 해당월, '200-02-01' "시작일",
   dateadd(dd,-1,'2000-03-01') "종료일"

--*******************************************************************
--      함수(Conversion)
--*******************************************************************
--cast, convert : 문자로 치환/number와 date를 문자로 치환.
--   /간단한 형 변환(CAST)
--   /날짜를 문자로 변환 시 여러가지 형식이 가능하므로 주로 사용하는 함수는(CONVERT)이다.
select convert(char, 123456789) result1,
   cast(123456789 as char) result2
--
select convert(varchar,456789) result1,
   cast(456789 as varchar) result2,
   str(456789, 10) result3
/* 숫자를 바꾸는 convert함수를 썼는데 2번째줄의 경우는 '300567'이라는 6섯자를
'char(4)' 4자로 바꾸라는 것: 이것은 말이 안되므로 * 표시가 프린트됨!(주의 사항) */
select convert(char(7),300567) result1,
   convert(char(4),300567) result2
--
select convert(char,getdate(),0) result1,
cast(getdate() as char) result2
--
select convert(CHAR, GETDATE(), 109) RESULT
select convert(CHAR, GETDATE(), 111) RESUlT
select convert(CHAR(10), GETDATE(), 121) RESULT
select convert(CHAR, GETDATE(), 114) RESULT

--
select convert(char, getdate(),0)
select convert(char, getdate(),1)
select convert(char, getdate(),2)
select convert(char, getdate(),3)
select convert(char, getdate(),4)
select convert(char, getdate(),5)
select convert(char, getdate(),6)
select convert(char, getdate(),7)
select convert(char, getdate(),8)
select convert(char, getdate(),9)
select convert(char, getdate(),10)
select convert(char, getdate(),11)
select convert(char, getdate(),12)
select convert(char, getdate(),13)
select convert(char, getdate(),14)
--다음 아래는 ERR...
select convert(char, getdate(),15)
select convert(char, getdate(),16)
select convert(char, getdate(),17)
select convert(char, getdate(),18)
select convert(char, getdate(),19)
--여기까지 ERR...
select convert(char, getdate(),20)
select convert(char, getdate(),21)
select convert(char, getdate(),22)
select convert(char, getdate(),23)
select convert(char, getdate(),24)
select convert(char, getdate(),25)
--여기서 또 아래는 ERR...
select convert(char, getdate(),26)
select convert(char, getdate(),27)
select convert(char, getdate(),28)
select convert(char, getdate(),29)
select convert(char, getdate(),30)
select convert(char, getdate(),31)
select convert(char, getdate(),32)
select convert(char, getdate(),33)
select convert(char, getdate(),34)
-- .
-- .
-- .
select convert(char, getdate(),99)
--99까지 ERR
--100단위로 위와 똑같은 숫자안에 포함되어 쓰인다.
--120과 121번의 경우는 우리나라에서 많이 쓴다. 그냥 21번을 쓰기도 한다.
select convert(char, getdate(),120)
select convert(char, getdate(),121)
/* 위에서 일반숫자형을 문자로 변환할 때 변환되어지려는 값과 변환하려는 값의
자릿숫자값이 다르면 에러가 났었지만, 날짜형은 그 자릿수만큼 잘라서 출력한다! */
--다음은 그 예이다.
--1. 날짜형 변환의 예)
select convert(char(10), getdate(),121)
--2. 숫자형 변환의 예)
select convert(char(7),300567) result1,
convert(char(4),300567) result2
--3. 문자형를 자르기 예)
select convert(char(10), 'abcdefghijklmnopqrstuvwxyz')

--122~에서부턴 형식이 맞지 않는다 ERR
select convert(char, getdate(),122)
select convert(char, getdate(),123)
select convert(char, getdate(),124)
select convert(char, getdate(),125)
   :
   :

--상품테이블에서 상품코드와 상품명을 연결하여 30자리로 치환하여 검색!
--Alias는 상품코드, 상품명, 치환상품명)
select prod_id 상품코드, prod_name 상품명,
   convert(char(30), prod_id + prod_name) 치환상품명
  from prod

--***********************************************
--      conversion
--***********************************************
--예제1) // datetime, smalldatetime, decimalzero, decimalpoint, numericzero, numericpoint

select convert(char(8), getdate(),112) result
--
select convert(datetime, '2001-01-01') DATETIME
--
select smalldatetime = convert(smalldatetime,'2001-01-01')
--
select decimalzero = convert(decimal(15),12345678912345)
--
select decimalpoint = convert(decimal(17,2),123456789012345.11)
--
select numericzero = convert(numeric,123456789012345)
select numericpoint = convert(numeric(17,2),123456789012345.11)

--예제2) // float, int, smallint, tinyint, money

select convert(float,123456789012345) float
--
select convert(float,10/3.0) float
--
select convert(int,1234567890) int
--
select convert(smallint,12345) smallint
--
select convert(tinyint, 123) tinyint
--
select convert(money,123456789012345) money

--***********************************************************************
--함수(Conversion) : 숫자로 치환
--***********************************************************************
--   숫자로 치환 // 모양만 수치인 문자를 NUMBER로 치환
--   /모양만 수치라면 연산에는 영향이 없다.
select convert(numeric,'123456.9') result1,
   cast('123456.9' as numeric) result2
--
select convert(numeric(10,2),'123456.9') result1,
   cast('123456.9' as numeric(10,2)) result2
--
select convert(float,'123456.9') result1,
   cast('123456.9' as float) result2
--
select convert(decimal,'123456.9') result1,
   cast('123456.9' as decimal) result2
--*****************************************************
/* 회원테이블에서 이쁜이 회원의 회원ID(b001) 2~4문자열을 숫자형으로 치환한 후 10을 더하여
 새로운 회원ID(b011)로 조합(Alias는 회원ID, 조합회원ID) */

회원ID     조합회원ID    -------   ------------      b001          b011    (1 row(s) affected)

--1. 방법1
select mem_id "회원ID",
   left(mem_id,1) + right(convert(char(4),right(mem_id,3) + 1010),3) "조합회원ID"
   from member
   where mem_name = '이쁜이'
--2. 방법2
select mem_id "회원ID",
   left(mem_id,1) + right(convert(char(4),substring(mem_id,2,4)+1010),3) "조합회원ID"
   from member
   where mem_name = '이쁜이'
--***********************************************************************
--      함수(conversion) : 날짜로 치환
--***********************************************************************
--날짜로 치환/모양만 날짜형인 문자를 DATE로 치환
select'19990101'result1,'1999-01-01'result2,
   '1999-01-01 00:00:00.000'result3
--
select convert(datetime,'20201025',112)" result1",
   convert(datetime,'20201025')" result2",
   convert(datetime,'2020-10-25 10:15:20.000') " result3",
   cast('2020-10-25 10:15:20.000' as datetime) " result4"
--
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
   convert(datetime,'19'+mem_regno1) 치환날짜
   from member
--1900년대 사람들만 있다고 가정했을 경우 19를 더해서 밀레니엄버그를 없앨 수 가 있다.
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 500일을 더한 날짜를 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
   convert(datetime,'19'+mem_regno1) + 500 치환날짜
   from member
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 '1974-01-01'부터 '1975-12-31'사이의 날짜를 검색!
--(Alias 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
   convert(datetime,'19'+mem_regno1) 치환날짜
   from member
   where convert(datetime,'19'+mem_regno1)between'1974-01-01'and'1975-12-31'
--회원테이블에서 생일을 문자로 치환한 후 LIKE '1975%'의 조건을 사용하여 해당회원을 검색!
--Alias는 회원명, 생일)
select mem_name 회원명, mem_bir 생일
   from member
   where convert(char, mem_bir, 121) like '1975%'




--******************************************************************
--      함수(NULL)
--******************************************************************
/* ## 데이터를 처리할 때 NULL값의 사용은 최대한 줄여야 하지만 사용해야 할 경우가 있다.
## 학생에 대한 정보를 입력할 때 '전화번호' 속성은 전화번호가 없는 학생이 있을 수가 있다 .이런 경우에는 NULL값을 사용한다.
## NULL값은 0.1과 같은 특정한 값이 아니고 아무 것도 없는 것을 뜻한다.
## SQL에서 NULL값을 허용하지 않는 속성에 대해 NULL값으로 수정하려 한다면 에러가 발생한다. */

--null값을 찾을 때에는 is null
select buyer_name buyer_charger
   from buyer
   where buyer_charger is null
--null인 값을 찾을 때 '='은 성립되지 않는다.
select buyer_name buyer_charger
   from buyer
   where buyer_charger=null
--
select buyer_name buyer_charger
   from buyer
   where buyer_charger =''
--null값이 아닌 값을 찾으려 할 때 is not null
select buyer_name buyer_charger
   from buyer
   where buyer_charger is not null
--ISNULL(c,d) / c가 NULL값이면 d값으로 치환
--null값일 때 '이름없다' 로 출력!
select buyer_name, buyer_charger, isnull(buyer_charger, '이름없다')
   from buyer
--null에다 100을 더하면 null이다.
select null + 100
--
select isnull(null,0)+100
--
select mem_name, mem_mileage, mem_mileage+100
   from member
--
select mem_name, mem_mileage, mem_mileage + 100
   from member
   where mem_name like '[바-•