SQL Server Date and time functions

Http://www.cnblogs.com/adandelion/archive/2006/11/08/554312.html

1, the common date method (below getdate () = ' 2006-11-08 13:37:56.233 ')

(1) datename (datepart, date)

Returns a String that represents the specified date portion of the specified date. DatePart See the list below.

SELECT Datename (Day,getdate ()) – returns 8

(2) DATEPART (DATEPART, date)

Returns an integer that represents the specified date portion of the specified date.

SELECT DATEPART (Year,getdate ()) – returns 2006

(3) DATEADD (datepart, number, date)

Returns a new datetime value that is added to the specified date after a time interval.

SELECT DATEADD (Week,1,getdate ())--date plus one week after the current date

(4) DATEDIFF (datepart, StartDate, EndDate)

Returns the number of date boundaries and time boundaries across two specified dates.

SELECT DATEDIFF (month, ' 2006-10-11 ', ' 2006-11-01 ')--return 1

(5) Day (date)

Returns an integer that represents the day datepart portion of the specified date.

SELECT Day (GetDate ()) – returns 8

(6) GETDATE ()

Returns the current system date and time as a datetime value in the SQL Server 2005 standard internal format.

SELECT GetDate ()--Return 2006-11-08 13:37:56.233

(7) MONTH (date)

Returns an integer that represents the month portion of a specified date.

SELECT MONTH (GETDATE ())--Return 11

(8) Year (date)

Returns an integer that represents the year part of the specified date.

SELECT year (GETDATE ())--Return 2006

2. Take a specific date

(1) get the current date is the day of the week

SELECT Datename (Weekday,getdate ())--wednesday

(2) calculate which day is the Monday of this week

Select DATEADD (Week, DATEDIFF (week, ' 1900-01-01 ', GETDATE ()), ' 1900-01-01 ')--return 2006-11-06 00:00:00.000 or SELECT DATEADD (Week, DATEDIFF (Week,0,getdate ()), 0)

(3) first day of the current quarter

SELECT DATEADD (Quarter, DATEDIFF (Quarter,0,getdate ()), 0)-Return 2006-10-01 00:00:00.000

(4) How to get the number of days in a month

SELECT Day (DateAdd (Ms,-3,dateadd (mm, DATEDIFF (m,0, ' 2006-02-03 ') +1,0))-Return 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 6th En 4 when @m between 7 and 9 then 7 else ten end

Select @time =datename (year,getdate ()) + '-' +convert (varchar), @m) + ' -01 ' Select DateDiff (Day, @time, DateAdd (mm,3,@ TIME)-Returns 92

(6) acquired date (YYYY-MM-DD)

SELECT CONVERT (VARCHAR), GETDATE (), 120) – Return 2006-11-08

3. Other

(1)--The following example specifies a date as a number. The database engine interprets 0 as January 1, 1900. SELECT MONTH (0), day (0), year (0) – return 1 1 1900

--The following two sentences are equivalent to select Datename (weekday,0) Select Datename (WEEKDAY, ' 1900-01-01 ')

(2) SET Datefirst {number | @number_var}

Set the first day of the week to a number from 1 to 7.

SET Datefirst 1--Indicates that the first day of the week is "Monday" Select Datename (Weekday,getdate ())--wednesday Select DATEPART (weekday,getdate ())-- Returns 3--View current settings select @ @DATEFIRST

(3) SET DateFormat {format | @format_var}

Sets the order of the date parts (month/day/year) that are used to enter datetime or smalldatetime data. ... Valid parameters include MDY, dmy, Ymd, YDM, MyD, and Dym. ... This setting is used only in the interpretation when converting a string to a date value. It does not affect the display of date values. ... Set DateFormat settings are set at execution or runtime, not at parse time. ... Set DateFormat overrides the implicit date format setting for set LANGUAGE. Here is an example:-Set date format to year, day, month. SET DateFormat YDM; GO DECLARE @datevar DATETIME; SET @datevar = ' 1998/31/12 '; SELECT @datevar as Datevar; GO-Set date format to year, month, day. SET DateFormat Ymd; GO DECLARE @datevar DATETIME; SET @datevar = ' 1998/12/31 '; SELECT @datevar as Datevar; GO
(4) List of date parts

Date part	Abbreviation
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	Accuracy
Datetime	January 1, 1753 to December 31, 9999	3.33 ms
smalldatetime	January 1, 1900 to June 6, 2079	1 minutes

SQL Server Date and time functions