2012.11.08 14:39

[MS-SQL] 날짜 차이 계산

DATEDIFF 함수

 

입력받은 두 날짜 또는 시간이 얼마나 차이가 나는지 계산

 

OPTION : DD(일), HH(시), MI(분), SS(초), MS(밀리초)

 

 

[예제]

 

SELECT DATEDIFF( DD, '2012-11-06 11:34', GETDATE())
→ 2

 

SELECT DATEDIFF( HH , '2012-11-07 11:30', '2012-11-08 14:44')
→ 27

 

SELECT DATEDIFF( MI , '2012-11-07 11:30', '2012-11-08 14:44')
→ 1634

 

SELECT DATEDIFF( HH, '11:30', '14:44')
→ 3

 

 

Trackback 0 Comment 0
2011.12.14 11:16

[MS-SQL] CONVERT를 이용한 날짜 형식 변경

기준시간 : 2011-12-14 13:07:49


SELECT CONVERT(VARCHAR, GETDATE(),   1) '  1'  ->  12/14/11
SELECT CONVERT(VARCHAR, GETDATE(),   2) '  2'  ->  11.12.14
SELECT CONVERT(VARCHAR, GETDATE(),   3) '  3'  ->  14/12/11
SELECT CONVERT(VARCHAR, GETDATE(),   4) '  4'  ->  14.12.11
SELECT CONVERT(VARCHAR, GETDATE(),   5) '  5'  ->  14-12-11
SELECT CONVERT(VARCHAR, GETDATE(),   8) '  8'  ->  13:07:49
SELECT CONVERT(VARCHAR, GETDATE(),   9) '  9'  ->  12 14 2011  1:07:49:520PM
SELECT CONVERT(VARCHAR, GETDATE(),  13) ' 13'  ->  14 12 2011 13:07:49:520
SELECT CONVERT(VARCHAR, GETDATE(),  14) ' 14'  ->  13:07:49:520
SELECT CONVERT(VARCHAR, GETDATE(),  20) ' 20'  ->  2011-12-14 13:07:49
SELECT CONVERT(VARCHAR, GETDATE(),  21) ' 21'  ->  2011-12-14 13:07:49.520
SELECT CONVERT(VARCHAR, GETDATE(),  22) ' 22'  ->  12/14/11  1:07:49 PM
SELECT CONVERT(VARCHAR, GETDATE(),  23) ' 23'  ->  2011-12-14
SELECT CONVERT(VARCHAR, GETDATE(),  25) ' 25'  ->  2011-12-14 13:07:49.520
SELECT CONVERT(VARCHAR, GETDATE(), 101) '101'  ->  12/14/2011
SELECT CONVERT(VARCHAR, GETDATE(), 102) '102'  ->  2011.12.14
SELECT CONVERT(VARCHAR, GETDATE(), 103) '103'  ->  14/12/2011
SELECT CONVERT(VARCHAR, GETDATE(), 104) '104'  ->  14.12.2011
SELECT CONVERT(VARCHAR, GETDATE(), 105) '105'  ->  14-12-2011
SELECT CONVERT(VARCHAR, GETDATE(), 111) '111'  ->  2011/12/14
SELECT CONVERT(VARCHAR, GETDATE(), 112) '112'  ->  20111214
SELECT CONVERT(VARCHAR, GETDATE(), 120) '120'  ->  2011-12-14 13:07:49
SELECT CONVERT(VARCHAR, GETDATE(), 121) '121'  ->  2011-12-14 13:07:49.520

'Database / Sql' 카테고리의 다른 글

[Oracle] 쿼리문 처리 과정  (0) 2012.02.16
[Oracle] USER_SOURCE  (0) 2011.12.27
[MS-SQL] CONVERT를 이용한 날짜 형식 변경  (1) 2011.12.14
[Oracle] 오라클 버전 확인  (0) 2011.11.16
[Oracle] ORA-01476 : 제수가 0  (0) 2011.11.16
[Oracle] RANK() 함수 Sample  (0) 2011.11.15
Trackback 0 Comment 1
  1. 2014.06.02 10:01 address edit & del reply

    비밀댓글입니다

2010.11.30 20:38

[MSSQL] 날짜 함수

SELECT DATEADD(MM, +1, '2010-08-08')

           , CONVERT(CHAR, DATEADD(MM, +1, '2010-08-08'), 23)

           , SUBSTRING(CONVERT(CHAR, DATEADD(MM, +1, '2010-08-08'), 23), 1, 7)+'-01'

           , GETDATE()

           , CONVERT(CHAR(10), GETDATE(), 121)

           , DATEDIFF(DD, SUBSTRING(CONVERT(CHAR, DATEADD(MM, +1, '2010-08-08'), 23), 1, 7)
              +'-01', CONVERT(CHAR(10), GETDATE(), 121))

           , DATEDIFF(DD, CONVERT(CHAR(10), GETDATE(), 121), SUBSTRING
           
(CONVERT(CHAR, DATEADD(MM, +1, '2010-08-08'), 23), 1, 7)+'-01')


결과  >>>

2010-09-08 00:00:00.000
2010-09-08                   
2010-09-01
2010-11-30 20:19:45.530
2010-11-30
90
-90

Trackback 0 Comment 0