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:
1.SELECT DATEPART(WEEK, 'Sep 24 2006')Results:
1.----------- 2.39Similarly, if you want to know which weekday a particular date falls on, you can use the WEEKDAY keyword with the DATEPART function:
1.SELECT DATEPART(WEEKDAY, 'Sep 24 2006 11:05:00' )Results:
1.-----------2.1DATENAME 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:
1.SELECT DATENAME(WEEKDAY, '9/25/2006 11:05:00PM' ), DATENAME (MONTH, '9/25/2006 11:05:00PM' )Results:
1.------------------------------ ------------------------------ 2.Monday SeptemberDAY, 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:
1.SELECT DAY('January 1, 2007'), MONTH('January 1, 2007'), YEAR('January 1, 2007')Results:
1.----------- ----------- ----------- 2.1 1 2007DAY, 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:
1.SELECT GETDATE() AS local_date, GETUTCDATE() AS UTC_dateResults:
1.local_date UTC_date 2.----------------------- ----------------------- 3.2006-03-19 13:08:18.050 2006-03-19 19:08:18.050DATEADD 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:
1.SELECT DATEADD(MONTH, 49, GETDATE())AS '49_months_from_now'Result:
1.49_months_from_now 2.----------------------- 3.2010-04-19 13:11:47.920DATEADD 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:
1.SELECT DISTINCT c.CurrencyName, YEAR(FullDateAlternateKey)2.AS year_traded 3.FROM factCurrencyRate a4.INNER JOIN DimTime b 5.ON a.TimeKey = b.TimeKey 6.INNER JOIN DimCurrency c7.ON a.CurrencyKey = c.CurrencyKey 8.WHERE FullDateAlternateKey > = DATEADD(YEAR, -2, GETDATE())Results:
01.CurrencyName year_traded 02.-------------------------------------------------- ----------- 03.Saudi Riyal 2004 04.Canadian Dollar 2004 05.Argentine Peso 2004 06.Bolivar 2004 07.United Kingdom Pound 2004 08.Yuan Renminbi 2004 09.Mexican Peso 2004 10.EURO 2004 11.Australian Dollar 2004 12.US Dollar 2004 13.Brazilian Real 2004 14.Yen 2004DATEDIFF 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):
1.SELECT DATEDIFF(DAY, b.FullDateAlternateKey, c.FullDateAlternateKey)2.AS delivery_time 3.FROM FactInternetSales a 4.INNER JOIN DimTime b5.ON a.OrderDateKey = b.TimeKey 6.INNER JOIN DimTime c7.ON a.ShipDateKey = c.TimeKeyDATEDIFF 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:
1.SELECT DATEDIFF (YEAR, '1/1/2002', '1/1/2003'), DATEDIFF (YEAR, '12/31/2002', '1/1/2003')Results:
1.----------- ----------- 2.1 1Whether 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.
No comments:
Post a Comment