DATEPART (Transact-SQL)

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

Transact-SQL Syntax Conventions

Syntax

DATEPART ( datepart , date )

Arguments

• datepart
  Is the parameter that specifies the part of the date to return. The following table lists the dateparts and abbreviations recognized by Microsoft SQL Server 2005.

  Datepart	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

  The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk,'Jan 1, xxxx') = 1, where xxxx is any year.

  The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST. This sets the first day of the week.

• date
  Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When you enter datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.

  If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two-digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that comes before the cutoff year. For example, if two-digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 50 is interpreted as 1950. To avoid ambiguity, use four-digit years.

  For more information about specifying time values, see Time Formats. For more information about specifying dates, see Date and Time (Transact-SQL).

Return Types

int

Remarks

The DAY, MONTH, and YEAR functions are synonyms for DATEPART(**dd,**date), DATEPART(mm,date), and DATEPART(yy, date), respectively.

Examples

The GETDATE function returns the current date. However, the complete date is not always the information needed for comparison; frequently only a part of the date is compared. The following example shows the output of GETDATE and DATEPART.

SELECT GETDATE() AS 'Current Date'
GO

Here is the result set.

Current Date                
--------------------------- 
Feb 18 1998 11:46PM         

SELECT DATEPART(month, GETDATE()) AS 'Month Number'
GO

Here is the result set.

Month Number 
------------ 
2            

The following example assumes the date May 29.

SELECT DATEPART(month, GETDATE())
GO

Here is the result set.

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

In the following example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.

SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)

Here is the result set.

----- ------ ------
1     1      1900

See Also

Reference

CAST and CONVERT (Transact-SQL)
Data Types (Transact-SQL)
Date and Time Functions (Transact-SQL)

Other Resources

ISO 8601 Format
Alphabetic Date Format
Numeric Date Format
ODBC Datetime Format
Time Formats
Unseparated String Format

Help and Information

Getting SQL Server 2005 Assistance