Summary of SQL time and date functions

I have added SQL time and date functions to my favorites. If you need these functions, please refer to the following section.

I have added SQL time and date functions to my favorites. If you need these functions, please refer to the following section.

① Getdate (): returns the current system date and time.

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
 
 
   
 
   
 
    
Current system time = GETDATE ()
 
   
 
  
 
 

　　

The Code is as follows:
② Dateadd (interval, number, date): return the new datetime after the specified date is added for a period of time. Select regressing one day = DATEADD (day,-1, getdate (), one hour forward = DATEADD (hour, 1, getdate ())

③ Datediff (interval, date1, date2): returns the difference between two specified time ranges.

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
 
 
   
 
   
 
    
DATEDIFF (DAY, '2017-12-5 ', '2017-1-1') -- Return: 27
Select days = datediff (DAY, '2017-01-01 ', '2017-01-01'), number of months = datediff (MONTH, '2017-01-01 ', '2017-01-01 ')
 
 
   
 
  
 
 
④ Datepart (interval, date) and datename (interval, date): return the value of the specified date interval. The difference is that datepart () returns an integer of the int type,

Datename returns a nvarchar string.

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
 
 
   
 
   
 
    
1 DATEPART (YEAR, '1970-12-5 ') -- datepart () returns: 2011 (int type)
2 DATENAME (YEAR, '1970-12-5 ') -- datename () returns: 2011 (nvarchar type)
3
4 DATEPART (MONTH, '2014-12-5 ') -- datepart () returns: 12 (int type)
5 DATENAME (MONTH, '2014-12-5 ') -- datename () returns: December (nvarchar type)
 
   
 
  
 
 
　　

Here, the interval parameters are listed. we can clearly see the difference between datepart () and datename.

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
 
 
   
 
   
 
    
1 select 'current time (datepart) '= GETDATE (), YEAR = DATEPART (YEAR, GETDATE (), QUARTER = DATEPART (QUARTER, GETDATE ()), month = DATEPART (Month, GETDATE ()),
2 weeks = DATEPART (WEEK, GETDATE (), Sunday = DATEPART (WEEKDAY, GETDATE (), DAY = DATEPART (DAY, GETDATE ()),
3-year days = DATEPART (DAYOFYEAR, GETDATE (), HOUR = DATEPART (HOUR, GETDATE (), MINUTE = DATEPART (MINUTE, GETDATE ()),
4 seconds = DATEPART (SECOND, GETDATE (), MILLISECOND = DATEPART (MILLISECOND, GETDATE ())
5
6 select 'current time (datename) '= GETDATE (), YEAR = DATENAME (YEAR, GETDATE (), QUARTER = DATENAME (QUARTER, GETDATE ()), month = DATENAME (Month, GETDATE ()),
WEEK 7 = DATENAME (WEEK, GETDATE (), Sunday = DATENAME (WEEKDAY, GETDATE (), DAY = DATENAME (DAY, GETDATE ()),
8-year days = DATENAME (DAYOFYEAR, GETDATE (), HOUR = DATENAME (HOUR, GETDATE (), MINUTE = DATENAME (MINUTE, GETDATE ()),
9 seconds = DATENAME (SECOND, GETDATE (), MILLISECOND = DATENAME (MILLISECOND, GETDATE ())
 
   
 
  
 
 

⑤ Year (), month (), day (): returns the year, month, and day of the specified date.

 
 
  
  
  
 
   
 
    
The Code is as follows:
 
    
 
 
   
 
   
 
    
1 YEAR ('1970-12-5 ') = DATEPART (YEAR, '1970-12-5') -- Return: 2011
2 MONTH ('2014-12-5 ') = DATEPART (Month, '2014-12-5') -- Return: 12
3 DAY ('2017-12-5 ') = DATEPART (DAY, '2017-12-5') -- Return: 5