SQL time and date functions

1. dateadd plus date functions

Select dateadd (day, 2, '2017-10-15 ') -- Return: 2004 00:00:00. 000

Of course, you can also add the select dateadd (month, 2, '2017-10-15 ') month ')

2. datediff returns the date between two dates.

Select datediff (day, '2017-09-01 ', '2017-09-18') -- Return: 17

Like dateadd, day can be changed to another

3. datepart returns an integer representing the specified date of the specified date.

Select DATEPART (month, '2014-10-15 ') -- Returns 10

4. Return the string representing the specified date part of the specified date by datename

Select datename (weekday, '2017-10-15 ') -- Return: Friday

 

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 as specified by interval
DateAdd (interval, number, date)	Add the date after number in the format specified by interval
DatePart (interval, date)	Returns the integer corresponding to the specified part of interval in date.
DateName (interval, date)	Returns the string name corresponding to the specified part of interval in date.

The value of interval is as follows:

 

Value	SQL Server)	(Access and ASP)	Description
Year	Yy	Yyyy	1753 ~ 9999
Quarter	Qq	Q	Season 1 ~ 4
Month	Mm	M	Month 1 ~ 12
Day of year	Dy	Y	The number of days in a year. The number of days in a year is 1-366.
Day	Dd	D	, 1-31
Weekday	Dw	W	The number of days in a week. The number of days in a week is 1-7.
Week	Wk	Ww	Week, week 0 ~ 51
Hour	Hh	H	Time 0 ~ 23
Minute	Mi	N	Minute 0 ~ 59
Second	Ss	S	Seconds 0 ~ 59
Millisecond	MS	-	Millisecond 0 ~ 999

The following methods are representative:

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

 

1. The first day of a month
Select DATEADD (mm, DATEDIFF (mm, 0, getdate (), 0)

2. Monday of the week
Select DATEADD (wk, DATEDIFF (wk, 0, getdate (), 0)

3. The first day of the year
Select DATEADD (yy, DATEDIFF (yy, 0, getdate (), 0)

4. The first day of the quarter
Select DATEADD (qq, DATEDIFF (qq, 0, getdate (), 0)

5. Midnight of the day
Select DATEADD (dd, DATEDIFF (dd, 0, getdate (), 0)

6. Last day of last month
Select dateadd (MS,-3, DATEADD (mm, DATEDIFF (mm, 0, getdate (), 0 ))

7. Last day of last year
Select dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate (), 0 ))

8. Last day of the month
Select dateadd (MS,-3, DATEADD (mm, DATEDIFF (m, 0, getdate () + 1, 0 ))

9. The last day of the year
Select dateadd (MS,-3, DATEADD (yy, DATEDIFF (yy, 0, getdate () + 1, 0 ))

10. The first Monday of this month
Select DATEADD (wk, DATEDIFF (wk, 0, dateadd (dd, 6-datepart (day, getdate (), getdate (), 0)