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) obtain the current day of the week

Select datename (weekday, getdate () -- Wednesday

(2) Calculate the day 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 the year, month, and day (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