SQL Server Date and Time Functions

SQL Server Date and Time Functions

1. Common date methods (the following getdate () = '2017-11-08 13:37:56. 123 ') (1)Datename(Datepart, date) Returns the string that represents the specified date part of the specified date. For details about datepart, see the following list. Select datename (day, getdate ()-returns 8 (2)Datepart(Datepart, date) Returns an integer that represents the specified date of a specified date. Select datepart (year, getdate ()-returns 2006 (3)Dateadd(Datepart, number, date) Returns the new datetime value after the specified date plus a time interval. Select dateadd (week, 1, getdate () -- the date after the current date plus one week (4)Datediff(Datepart, startdate, enddate) Returns the number of date boundary and time boundary that span two specified dates. Select datediff (month, '2014-10-11 ', '2014-11-01') -- returns 1 (5)Day(Date) Returns an integer representing the day datepart part of the specified date. Select Day (getdate ()-returns 8 (6)Getdate() Returns the current system date and time in the standard internal format of SQL Server 2005 with datetime value. Select getdate () -- returns 13:37:56. 233 (7)Month(Date) Returns an integer that represents the month of the specified date. Select month (getdate () -- returns 11 (8)Year(Date) Returns an integer that represents the year of the specified date. Select Year (getdate () -- returns 2006 2. Get a specific date (1)Returns the current day of the week. Select datename (weekday, getdate () -- Wednesday (2)Calculate which day is Monday of the week Select dateadd (Week, datediff (Week, '2017-01-01 ', getdate (), '2017-01-01') -- returns 00:00:00. 000 Or Select dateadd (Week, datediff (Week, 0, getdate (), 0) (3)The first day of the current quarter Select dateadd (quarter, datediff (quarter, 0, getdate (), 0)-returns 00:00:00. 000 (4)How to obtain the number of days in a month Select Day (dateadd (MS,-3, dateadd (mm, datediff (M, 0, '2017-02-03 ') + 2006)-returns 28 (5) How many days of a quarter Declare @ M tinyint, @ time smalldatetime Select @ M = month (getdate ()) Select @ M = case when @ m between 1 and 3 then 1 When @ m between 4 and 6 then 4 When @ m between 7 and 9 then 7 Else 10 end Select @ time = datename (year, getdate () + '-' + convert (varchar (10), @ M) + '-01' Select datediff (day, @ time, dateadd (mm, 3, @ time)-return 92 (6)Obtain yyyy-mm-dd) Select convert (varchar (10), getdate (), 120)-returns 3. Others (1) -- The following example specifies the date as a number. The Database Engine interpreted 0 as January 1, 1900. Select month (0), Day (0), Year (0)-1 1 1900 is returned -- The following two statements are equivalent. Select datename (weekday, 0) Select datename (weekday, '2017-01-01 ') (2)Set datefirst{Number | @ number_var} Set the first day of a week to a number ranging from 1 to 7. Set datefirst 1 -- indicates that the first day of a week is "Monday" Select datename (weekday, getdate () -- Wednesday Select datepart (weekday, getdate () -- 3 is returned. -- View Current Settings Select @ datefirst (3)Set dateformat{Format | @ format_var} Set the order of dates (Months, days, and years) used to input datetime or smalldatetime data. ... Valid parameters include mdy, DMY, ymd, ydm, MYD, and dym. ... This setting is only used in the interpretation of converting a string to a date value. It does not affect the display of date values. ... Set dateformat is set during execution or runtime, rather than during analysis. ... Set dateformat will overwrite the implicit date format setting of set language. The following is an example: -- Set date format to year, day, month. Set dateformat ydm; Go Declare @ datevar datetime; Set @ datevar = '2014/1/12 '; Select @ datevar as datevar; Go -- Set date format to year, month, day. Set dateformat ymd; Go Declare @ datevar datetime; Set @ datevar = '2014/1/31 '; Select @ datevar as datevar; Go (4)List of dates Date Abbreviations Year YY, yyyy Quarter QQ, Q Month Mm, m Dayofyear Dy, y Day DD, d Week Wk, WW Weekday DW Hour HH Minute Mi, n Second SS, S Millisecond MS   Data Type Range Precision Datetime January 1, 1753 Month 1 December 9999 31st 3.33 ms Smalldatetime January 1, 1900 Month 1 September 6, 2079 6th 1 minute