Built-in Functions - Date and Time Functions

From SQLServerPedia

Jump to: navigation, search

See Also: Main_Page - Transact SQL Coding Techniques - Reusable Coding Techniques - Functions - SQL Server 2005 Built-Ins

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

Contents

DATEPART Function

The DATEPART function allows retrieving any part of the date and time variable provided. This function is deterministic except when used with days of the week.

The DATEPART function takes two parameters: the part of the date that you want to retrieve and the date itself. The DATEPART function returns an integer representing any of the following parts of the supplied date: year, quarter, month, day of the year, day, week number, weekday number, hour, minute, second, or millisecond.

For example, suppose that you want to retrieve the week number of September 24, 2000. You can use the following code: 

SELECT DATEPART(WEEK, 'Sep 24 2006')

Results: 

-----------  
39

Similarly, if you want to know which weekday a particular date falls on, you can use the WEEKDAY keyword with the DATEPART function: 

SELECT DATEPART(WEEKDAY, 'Sep 24 2006 11:05:00' )

Results: 

-----------
1


DATENAME Function

The DATENAME nondeterministic function returns the name of the portion of the date and time variable. Just like the DATEPART function, the DATENAME function accepts two parameters: the portion of the date that you want to retrieve and the date. The DATENAME function can be used to retrieve any of the following: name of the year, quarter, month, day of the year, day, week, weekday, hour, minute, second, or millisecond of the specified date. For instance, you can determine the weekday name as well as the month name of a given date as follows: 

SELECT DATENAME(WEEKDAY, '9/25/2006 11:05:00PM' ),  DATENAME (MONTH, '9/25/2006 11:05:00PM' )


Results: 

------------------------------ ------------------------------  
Monday                         September

DAY, MONTH, and YEAR Functions

DAY, MONTH and YEAR functions are deterministic. Each of these accepts a single date value as a parameter and returns respective portions of the date as an integer. The following example shows how various portions of the date and time value can be retrieved using these functions: 

SELECT   DAY('January 1, 2007'),   MONTH('January 1, 2007'),   YEAR('January 1, 2007')


Results: 

----------- ----------- -----------  
1           1           2007


DAY, MONTH and YEAR functions are functionally equivalent to executing DATEPART function with day, month or year as the first parameter, respectively.

GETDATE and GETUTCDATE Functions

GETDATE and GETUTCDATE functions both return the current date and time. However, GETUTCDATE returns the current Universal Time Coordinate (UTC) time, whereas GETDATE returns the date and time on the computer where SQL Server is running. The GETUTCDATE() function compares the time zone of SQL Server computer with the UTC time zone. Neither of these functions accepts parameters, and they are both non-deterministic. Here is an example: 

SELECT   GETDATE() AS local_date,   GETUTCDATE() AS UTC_date


Results: 

local_date              UTC_date  
----------------------- -----------------------  
2006-03-19 13:08:18.050 2006-03-19 19:08:18.050

DATEADD Functions

DATEADD function is deterministic; it adds a certain period of time to the existing date and time value. For instance, the following query determines the date 49 months from the current date: 

SELECT DATEADD(MONTH, 49, GETDATE())AS '49_months_from_now'


Result: 

49_months_from_now  
-----------------------  
2010-04-19 13:11:47.920


DATEADD is also often used to determine which rows qualify for a particular report. For example, the following report retrieves the currencies that have been traded in past two years by using (-2) as the second DATEADD parameter: 

SELECT   DISTINCT   c.CurrencyName,   YEAR(FullDateAlternateKey)
AS year_traded 
FROM factCurrencyRate a
INNER JOIN DimTime b 
ON a.TimeKey = b.TimeKey 
INNER JOIN DimCurrency c
ON a.CurrencyKey = c.CurrencyKey 
WHERE  FullDateAlternateKey > =  DATEADD(YEAR, -2, GETDATE())


Results: 

CurrencyName                                       year_traded  
-------------------------------------------------- -----------  
Saudi Riyal                                        2004  
Canadian Dollar                                    2004  
Argentine Peso                                     2004  
Bolivar                                            2004  
United Kingdom Pound                               2004  
Yuan Renminbi                                      2004  
Mexican Peso                                       2004  
EURO                                               2004  
Australian Dollar                                  2004  
US Dollar                                          2004  
Brazilian Real                                     2004  
Yen                                                2004

DATEDIFF Function

DATEDIFF function is deterministic; it accepts two DATETIME values and a date portion (minute, hour, day, month, etc) as parameters. DATEDIFF() determines the difference between the two date values passed, expressed in the date portion specified. Notice also that start date should come before the end date, if you'd like to see positive numbers in the result set. For example the following query will determine the number of days it took for shipping the internet sales orders (delivery time): 

SELECT   DATEDIFF(DAY, b.FullDateAlternateKey, c.FullDateAlternateKey) 
AS delivery_time 
FROM FactInternetSales a 
INNER JOIN DimTime b
ON a.OrderDateKey = b.TimeKey 
INNER JOIN DimTime c
ON a.ShipDateKey = c.TimeKey


DATEDIFF will work even if the end date is earlier than the start date - you will simply see the negative values in the output. Also keep in mind that DATEDIFF returns an INTEGER - it does not calculate fractions for you. This can cause unexpected results, like in the following query: 

SELECT   DATEDIFF (YEAR, '1/1/2002', '1/1/2003'),   DATEDIFF (YEAR, '12/31/2002', '1/1/2003')


Results: 

----------- -----------  
1              1


Whether you're comparing the first or last day of 2002 with January 1st of 2003 you'll see the same output from the DATEDIFF function. In some cases that's perfect because you simply want to use DATEDIFF to find orders within the current year. However, the time difference between December 31st 2002 and January 1st 2003 is only 24 hours; so if you care about accuracy then be sure to use the smallest fraction of the time possible when using DATEDIFF.

More SQL Server Functions

Retrieved from "http://sqlserverpedia.com/wiki/Built-in_Functions_-_Date_and_Time_Functions"