Sqlserver datepart function usage

Datepart

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

Syntax

Datepart(Datepart,Date)

Parameters

Datepart

Is a parameter that specifies the date part to be returned. The following table lists the dates and abbreviations recognized by Microsoft SQL Server.

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

Week(Wk, WW) The date part reflects the changes made to set datefirst. Any year's January 1 definesWeekStart Number of the date part, for example, datepart (WK, 'Jan 1, xxxx') = 1. xxxx indicates any year.

Weekday(DW) Returns the number of days corresponding to the day of the week, for example, Sunday = 1, Saturday = 7.WeekdayThe number of data generated in the date part depends on the set datefirst value. This command sets the first day of the week.

Date

YesDatetimeOrSmalldatetimeValue or date format string expression. For the date after January 1, January 1, 1753DatetimeData type. The earlier date is stored as character data. When you enterDatetimeAlways put the value in quotation marks. BecauseSmalldatetimeOnly accurate to minutes, so when usingSmalldatetimeThe second and millisecond values are always 0.

If only the last two digits of the year are specified, the number of the last two digits smaller than or equal to the value of the "two-digit year deadline" configuration option is in the same century as that of the end year. The last two-digit number greater than the value of this option is in the century before the end of the century. For example, ifTwo digit year cutoffIs 2049 (default), then 49 is interpreted as 2049,2050 is interpreted as 1950. To avoid blurring, use a four-digit year.

For more information about the time value, see time format. For more information about the specified date, see datetime and smalldatetime.

Return type

Int

Note

The functions of day, month, and year are datepart (Dd,Date), Datepart (Mm,Date), And datepart (YY,Date.

Example

The getdate function returns the current date. However, the comparison does not always require complete date information (usually only a part of the date is compared ). This example shows the output of getdate and datepart.

 
Select getdate () as 'current date' go
 

The following is the result set:

Current date --------------------------- Feb 18 1998 11: 46 pmselect datepart (month, getdate () as 'month number' go
 

The following is the result set:

 
Month number ------------ 2
 

This example assumes that the date is July 22, May 29.

 
Select datepart (month, getdate () Go
 

The following is the result set:

 
----------- 5 (1 row (s) affected)
 

In this example, the date is represented by a number. Note: SQL Server interprets 0 as 01/01/1900.

 
Select datepart (M, 0), datepart (D, 0), datepart (YY, 0)
 

The following is the result set:

 
----- ------ 1 1900
From: http://www.cnblogs.com/jackrebel/archive/2008/04/17/1157540.html