Common date and time functions in SQLserver

Statistics are the most common and essential in the database. Some SQL statements encountered in statistics using the Teechat control some time ago are sorted out. Key commonly used functions include datediff () and datepart (), the following describes these functions.

The code is as follows:

Copy code

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)

The time calculation functions include: Year (yy), quarter (q), month (m), Week (ww), period (w), Day (d ), hour (hh), Minute (n), second (s)

Getdate (): returns the current date.

Get the number of days of the current month:

The code is as follows:	Copy code
Select datediff (d, getdate (), dateadd (m, 1, getdate ()))

Obtain the current year, quarter, month, week, day, hour, minute, and second:

The code is as follows:	Copy code
Select datepart (yy, getdate () // year Select datepart (q, getdate () // quarter Select datepart (m, getdate () // month Select datepart (ww, getdate () // week Select datepart (w, getdate () // week, starting from 0. Select datepart (d, getdate () // day Select datepart (hh, getdate () // Select datepart (n, getdate () // minute Select datepart (s, getdate () // second

Obtain the date of the last five days of the current day:

The code is as follows:	Copy code
Select dateadd (dd, 5, getdate ()) Select dateadd (dd,-5, getdate ())

Obtain the year, month, week, day, hour, minute, and second of the difference between the current date and the target date:

The code is as follows:

Copy code

Select datediff (yy, '2017-01-01 14:12:01 ', getdate ()) Select datediff (q, '2017-01-01 14:12:01 ', getdate ()) Select datediff (m, '2017-01-01 14:12:01 ', getdate ()) Select datediff (ww, '2017-01-01 14:12:01 ', getdate ()) Select datediff (d, '2017-01-01 14:12:01 ', getdate ()) Select datediff (hh, '2017-01-01 14:12:01 ', getdate ()) Select datediff (n, '2017-01-01 14:12:01 ', getdate ()) Select datediff (s, '2017-01-01 14:12:01 ', getdate ())

Obtain the information added for the current month:

The code is as follows:	Copy code
Select count (*) as num, Origin from ks_article month (getdate () = DatePart ("m", adddate) group by Origin

Appendix ql server 2008 r2 date and time functions

The code is as follows:

Copy code

/* Environment: win7 + SQL server 2008 r2 Author: CC Desctiption: Sort common date functions: DAY, MONTH, YEAR, DATEPART DATEADD, DATENAME, DATEDIFF, isdate usage */ -- Intercept a period of years, months, and days. Select DAY (GETDATE ()) , MONTH (GETDATE () -- when the parameter of the MONTH () function is an integer, all return an integer of 1, that is, SQL Server considers it as January 1, January 1900. , YEAR (GETDATE () -- The date value should be between January 1, 1753 and January 1, 9999. This is the date range recognized by the SQL Server System. Otherwise, an error occurs. -- Use the DATEPART function UNION ALL Select datepart (DAY, GETDATE ()) , DATEPART (MONTH, GETDATE ()) , DATEPART (YEAR, GETDATE ()) /* (No column name) 7 2012 7 2012 */ Select datename (DD, GETDATE () -- Return type: bytes /* (No column name) */ -- Use dateadd to obtain the next time or previous time and date Select DATEADD (DD, 1, getdate () -- next day of the current time , DATEADD (MM, 1, getdate () -- Next month of the current time , DATEADD (YY, 1, getdate () -- next year of the current time , DATEADD (DD,-1, getdate () -- The day before the current time , DATEADD (MM,-1, getdate () -- Previous month of the current time , DATEADD (YY,-1, getdate () -- The year before the current time         -- 15:47:05. 663 15:47:05. 663 15:47:05. 663 15:47:05. 663 15:47:05. 663 15:47:05. 663 Select datediff (DD, '2017-07-11 15:03:11. 100', GETDATE () -- time difference -- 6 Select datename (dayofyear, GETDATE (), DATEDIFF (dd, '2017-01-01 ', getdate ()) -- 199 198 Select datename (year, GETDATE ()) , DATENAME (month, GETDATE ()) , DATENAME (day, GETDATE ()) , DATENAME (week, GETDATE ()) , DATENAME (dayofyear, GETDATE ()) , DATENAME (weekday, GETDATE ()); -- 2012 July 17 29 199 Tuesday        Select datename (hour, GETDATE () -- The hour of the current day , DATENAME (minute, GETDATE () -- The fraction of the hour of the day , DATENAME (second, GETDATE (); -- the second of the hour minute of the day --- 15 48 9 Select isdate ('2014-07-17 ') UNION ALL Select isdate ('2014-07-17 15:12:00 ') UNION ALL Select isdate (111) /*   */ /* Function syntax return value returned data type DAY (date) returns an integer that represents the "DAY" part of the specified date. Int is deterministic. MONTH (date) returns an integer that represents the "MONTH" part of the specified date. Int is deterministic. YEAR (date) returns an integer that represents the "YEAR" part of the specified date. Int is deterministic. DATENAME (datepart, date) returns the string representing the specified datepart of the specified date. Nvarchar DATEPART (datepart, date) returns an integer that represents the specified datepart of a specified date. Int DATEDIFF (datepart, startdate, enddate) returns the number of datepart boundaries between two specified dates. Int is deterministic. DATEADD (datepart, number, date) returns a new datetime value by adding a time interval to the specified datepart of the specified date. ISDATE (expression) determines whether the datetime or smalldatetime input expression is a valid date or time value. Int is only used with the CONVERT function. If the CONVERT style parameter is specified and the style is not equal to 0, 100, 9, or 109, ISDATE is determined. */