BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server DateTime Information Functions

The fifty-first part of the SQL Server Programming Fundamentals tutorial is the first of two articles describing Transact-SQL functions that process date and time information. This instalment examines functions that extract elements from DateTimes.

DateTime Functions

In this article and the next in the tutorial we will examine the SQL Server 2005 Transact-SQL (T-SQL) functions that process date and time data. This article describes five functions that allow a part of a date and time value to be extracted and used in queries, data manipulation and other T-SQL statements. The second part will review functions that are used to manipulate date and time information. In addition, you may wish to read the article, "Get the Current SQL Server Date and Time", which describes two further DateTime functions.

Day

The Day function returns an integer that represents the day of the month from a DateTime or SmallDateTime value. The returned value is a number between one and thirty-one. We can demonstrate the function by executing the following sample:

PRINT day('5 December 2009')    -- 5

Month

The Month function is similar in operation to Day. However, this functions returns and integer between one and twelve that represents the month part of the provided date.

PRINT month('5 December 2009')    -- 12

Year

The Year function is used in a similar manner to both Day and Month. It returns an integer representing the year of a provided date.

PRINT year('5 December 2009')    -- 2009

DatePart

The DatePart function provides a more general version of the Day, Month and Year functions. In addition to passing the date and time from which to extract information, with DatePart you also provide a short character code that determines which element of the date and time you wish to obtain. The returned value is an integer representing that part of the date or time.

The following sample shows how to extract the hour from a date and time.

PRINT datepart(hh, '5 December 2009 13:24:56')  -- 13

The complete list of available elements and their character codes is shown below. Some date parts can be extracted using more than one code. In those cases a comma-separated list of the codes is shown in the first column of the table. The example results may vary according to your SQL Server configuration.

CodeDate PartExample for 5 December 2009 13:24:56.5
yy, yyyyYear2009
q, qqQuarter4
m, mmMonth12
dy, yDay of Year339
d, ddDay5
wk, wwWeek*49
dwWeekday**7
hhHour13
mi, nMinute24
s, ssSecond56
msMillisecond500

* The week counter starts from one for the week containing 1 January. Week two starts on the first day of the following week. This means that week one does not necessarily contain seven days.

** The weekday value assigns a number to each day of the week. For example, 1 is often used to represent Sunday. This value can vary according to the configuration of the SQL Server system.

DateName

The DateName function is similar to DatePart. It extracts a specified part of a date or time using the same set of character codes. The difference between the two functions is that DateName returns an NVarChar result. In most cases the result contains a string representation of a number. When extracting the month or the weekday, the result contains the name of the date part:

PRINT datename(hh, '5 December 2009 13:24:56')  -- 13
PRINT datename(mm, '5 December 2009 13:24:56')  -- December
PRINT datename(dw, '5 December 2009 13:24:56')  -- Saturday
5 December 2009