Home Library Learn Downloads Support Community
Sign in | United States - English | |
77 out of 121 rated this helpful - Rate this topic

DATEPART (Transact-SQL)

SQL Server 2005

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

Topic link icon Transact-SQL Syntax Conventions

Copy 

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.

Copy 
SELECT GETDATE() AS 'Current Date'
GO

Here is the result set.

Copy 
Current Date                
--------------------------- 
Feb 18 1998 11:46PM
Copy 
SELECT DATEPART(month, GETDATE()) AS 'Month Number'
GO

Here is the result set.

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

The following example assumes the date May 29.

Copy 
SELECT DATEPART(month, GETDATE())
GO

Here is the result set.

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

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

Here is the result set.

Copy 
----- ------ ------
1     1      1900
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
Caution with DatePart / DateDiff
As with the above excellent example, making a nice format is essential and calculations can be easily implemented using DATEPART with the addition of DATEDIFF.

Bad Example;
(May return negative numbers. Notice the -6 value (minus 6 months implied), will not work as intended if the month is say May / 5th month would produce -1 month value)

SELECT CAST(DATEPART(dd, GETDATE()) as varchar(2))
+ '/' + CAST(DATEPART(mm, GETDATE())-6 as varchar(2))
+ '/' + CAST(DATEPART(yyyy, GETDATE()) as varchar(4)) [Today]

Another example would be to utilise the TIMESTAMPDIFF SQL Function as such;

SELECT *, {FN TIMESTAMPDIFF(SQL_TSI_DAY, MEETINGDATE, GETDATE())} as Period
from [StaffMeetings]
WHERE {FN TIMESTAMPDIFF(SQL_TSI_DAY, MEETINGDATE, GETDATE())} < 182.5
AND NOT {FN TIMESTAMPDIFF(SQL_TSI_DAY, MEETINGDATE, GETDATE())} >= 182.5

Assuming that 182.5 = 6 months and that [StaffMeetings] actually contains some dates that span a period this would be another method for performing the calculation inside of the SQL engine and reducing the need to process back on the client.

http://msdn.microsoft.com/en-us/library/ms378045.aspx

:-)


History
Format date output as MM/DD/YYYY using DATEPART and CAST
Given a column SALESDATE in table SALES, this select will produce sales dates formatted in nice, 10-character strings of the form MM/DD/YYYY:
SELECT CAST(DATEPART(mm, SALESDATE) as varchar(2)) + '/' + CAST(DATEPART(dd, SALESDATE) as varchar(2)) + '/' + CAST(DATEPART(yyyy, SALESDATE) as varchar(4)) AS SALESDATE

This is handy enough that I thought someone else might like to find it when they're googling.

History
DATAPART web page http://msdn2.microsoft.com/en-us/library/ms174420.aspx - Error
The Datepart table above is incorrectly formated and needs to be fixed by Microsoft so that it can be properly read.
History