[MS SQL] 날짜/시간 계산

 

현재 날짜 시간

--Result : 2013-04-05 14:38:35.040 
Select '현재날짜시간', GETDATE()

 

 날짜및시간계산함수

--Result : 2014-04-05 14:38:35.040 
Select '년', DATEADD(Year, 1, GETDATE())       
--Result : 2013-05-05 14:38:35.040                                       
Select '월', DATEADD(Month, 1, GETDATE()) 
--Result : 2013-04-06 14:38:35.040                                              
Select '일', DATEADD(Day, 1, GETDATE()) 
--Result : 2013-04-12 14:38:35.040                                                    
Select '주', DATEADD(Week, 1, GETDATE()) 
--Result : 2013-04-05 15:38:35.040                                              
Select '시', DATEADD(Hour, 1, GETDATE()) 
--Result : 2013-04-05 14:39:35.040                                              
Select '분', DATEADD(minute, 1, GETDATE()) 
--Result : 2013-04-05 14:38:36.040                                              
Select '초', DATEADD(second, 1, GETDATE())                  
--Result : 2013-04-05 14:38:35.040                          
Select '밀리초', DATEADD(millisecond, 1, GETDATE()) 
--Result : 2013-07-05 14:38:35.040                                
Select '분기', DATEADD(quarter, 1, GETDATE()) 
--Result : 2013-04-06 14:38:35.040                                       
Select '년의일', DATEADD(dayofyear,1, GETDATE())

 

날짜및시간차이함수

--Result : 2 
Select '년', DATEDIFF(year, '2012-01-01', '2014-02-01')     
--Result : 3               
Select '월',  DATEDIFF(Month, '2012-01-01', '2012-04-01') 
--Result : 62              
Select '일',  DATEDIFF(Day, '2012-02-01', '2012-04-03') 
--Result : 4                     
Select '주',  DATEDIFF(Week, '2012-01-01', '2012-02-01') 
--Result : 744                   
Select '시',  DATEDIFF(Hour, '2012-01-01', '2012-02-01') 
--Result : 44640                 
Select '분',  DATEDIFF(minute, '2012-01-01', '2012-02-01') 
--Result : 2678400               
Select '초',  DATEDIFF(second, '2012-01-01', '2012-02-01') 
--Result :    86400000                   
Select '밀리초',  DATEDIFF(millisecond, '2012-01-01', '2012-01-02') 
--Result : 1 
Select '분기',  DATEDIFF(quarter, '2012-01-01', '2012-04-01') 
--Result : 62       
Select '년의일',  DATEDIFF(dayofyear, '2012-02-01', '2012-04-03')

 

지정일자의datepart 값반환함수

--Result : 2013 
select '년', DATEpart(year, GETDATE()) 
--Result : 4                                                
select '월', DATEpart(Month, GETDATE()) 
--Result : 5                                                
select '일', DATEpart(Day, GETDATE()) 
--Result : 14                                               
select '주', DATEpart(Week, GETDATE())                                                 
--Result : 6    /   1:일,2:월,3:화,4:수,5:목,6:금,7:토 
select '요일', DATEpart(WeekDay, GETDATE())    
--Result : 14                                 
select '시', DATEpart(Hour, GETDATE()) 
--Result : 38                                               
select '분', DATEpart(minute, GETDATE()) 
--Result : 35                                        
select '초', DATEpart(second, GETDATE()) 
--Result : 40                                        
select '밀리초', DATEpart(millisecond, GETDATE()) 
--Result : 2 
select '분기', DATEpart(quarter, GETDATE()) 
--Result : 95 
select '년의일', DATEpart(dayofyear, GETDATE())

 

 

[출처] http://phm0515.blog.me/20155265045