Common SQL date and time functions

I. Date and time functions in SQL Server
1. Current system date and time
Select getdate ()

2. dateadd returns a new datetime value based on a period of time added to the specified date.
For example, add 2 days to the date
Select dateadd (day, 2, '2017-10-15 ') -- Return: 2004 00:00:00. 000

3. datediff returns the number of date and time boundaries across two specified dates.
Select datediff (day, '2017-09-01 ', '2017-09-18') -- Return: 17

4. datepart returns an integer representing the specified date of the specified date.
Select DATEPART (month, '2014-10-15 ') -- Returns 10

5. Return the string representing the specified date part of the specified date by datename
Select datename (weekday, '2017-10-15 ') -- Return: Friday

6. day (), month (), year () -- compare it with datepart.

Select current date = convert (varchar (10), getdate (), 120)
, Current time = convert (varchar (8), getdate (), 114)

Select datename (dw, '2017-10-15 ')

Select the week of the current year = datename (week, '2017-10-15 ')
, Today is the day of the week = datename (weekday, '2017-10-15 ')
II. SQL date format conversion
Select CONVERT (varchar, getdate (), 120)
11:06:08
Select replace (CONVERT (varchar, getdate (), 120 ),'-',''),'',''),':','')
20040912110608
 
Select CONVERT (varchar (12), getdate (), 111)
2004/09/12
 
Select CONVERT (varchar (12), getdate (), 112)
20040912

Select CONVERT (varchar (12), getdate (), 102)
2004.09.12
 
Other date format conversion methods that are not commonly used:

Select CONVERT (varchar (12), getdate (), 101)
09/12/2004

Select CONVERT (varchar (12), getdate (), 103)
12/09/2004

Select CONVERT (varchar (12), getdate (), 104)
12.09.2004

Select CONVERT (varchar (12), getdate (), 105)
12-09-2004

Select CONVERT (varchar (12), getdate (), 106)
12 09 2004

Select CONVERT (varchar (12), getdate (), 107)
09 12,200 4

Select CONVERT (varchar (12), getdate (), 108)
11:06:08
 
Select CONVERT (varchar (12), getdate (), 109)
09 12, 2004 1

Select CONVERT (varchar (12), getdate (), 110)
09-12-2004

Select CONVERT (varchar (12), getdate (), 113)
12 09 2004 1

Select CONVERT (varchar (12), getdate (), 114)
11:06:08. 177

Example:
1. GetDate () is used for SQL server: select GetDate ()

2. DateDiff ('s ', '2017-07-20', '2017-7-25 22:56:32 ') returns 2005 seconds
DateDiff ('D', '2017-07-20 ', '2017-7-25 22:56:32') returns 5 days

3. DatePart ('W', '2017-7-25 22:56:32 ') returns 2 Monday (1 on Sunday and 7 on Saturday)
DatePart ('D', '2017-7-25 22:56:32 ') returns 25, that is, 25.
DatePart ('Y', '2017-7-25 22:56:32 ') returns 2005, that is, 206 days of the year.
DatePart ('yyyy', '2017-7-25 22:56:32 ') returns 2005, or 2005.
Diagram

Function parameters/functions
GetDate () returns the current date and time of the system.
DateDiff (interval, date1, date2) returns the difference date2-date1 between date2 and date1 in the way specified by interval
DateAdd (interval, number, date) in the format specified by interval, plus the date after the number
DatePart (interval, date) returns the integer corresponding to the specified part of the date.
DateName (interval, date) returns the string name corresponding to the specified part of interval in date.

 

The value of interval is as follows:

 

SQL Server Access and ASP
Year Yy yyyy-1753 ~ 9999
Quarter Qq q Quarter 1 ~ 4
Month Mm m Month 1 ~ 12
Day of year Dy y the number of days of the year, the Day of the year 1-366
Day Dd d, 1-31
Weekday Dw w the number of days in a week, the day of the Week 1-7
Week Wk ww Week, the Week of the year 0 ~ 51
Hour Hh h 0 ~ 23
Minute Mi n minutes 0 ~ 59
Second Ss s 0 ~ 59
Millisecond MS-Millisecond 0 ~ 999