SQL server date and time functions

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) description
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

In the access and asp tutorials, date () and now () are used to obtain the system date and time. DateDiff, DateAdd, and DatePart can also be used in Access and asp. The usage of these functions is similar.

 

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) description
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
Access and asp use date () and now () to obtain the system date and time. DateDiff, DateAdd, and DatePart can also be used in Access and asp. The usage of these functions is similar.

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.

CONVERT (datetime, CONVERT (varchar, GETDATE (), 103), 103)

CONVERT (datetime, CONVERT (varchar, GETDATE (), 103)
And (isdiscontinue! = 1) or (isdiscontinue = 1 and DiscontinueDate> = CONVERT (datetime, CONVERT (varchar, GETDATE (), 101 ))))

Select DATEDIFF (minute, convert (datetime,
'2017-01-01' + SUBSTRING ('20170901', 1, 2) + ':' +
SUBSTRING ('20140901', 3, 2 ),
120), convert (datetime,
'2017-01-01' + SUBSTRING ('20170901', 1, 2) + ':' +
SUBSTRING ('20140901', 3, 2 ),
120 ))