Export (0) Print
Expand All
Expand Minimize

DATEPART (Transact-SQL)

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

Topic link icon Transact-SQL Syntax Conventions


DATEPART ( datepart , date )

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).

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

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
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft