This article will be the last in my data/time series, and will discuss the last few date functions I have yet to cover in this series. I will discuss how to use the DATEDIFF and DATEADD functions to perform different date related mathematical calculations. I will also talk about what universal time is and discuss how the GETDATE and GETUTCDATE functions work.

If your application needs to take a date entered, or a date stored in the database and calculate a date in the future or the past, or compare two dates to determine the number of days between them, then the DATEADD and DATEDIFF functions can be used to perform these tasks.

DATEADD

The DATEADD function can be used to add or subtract a number of days, years, or some other time related datepart from a datetime value. Here is how to call the DATEADD function.

	DATEADD ( datepart , number, date ) 

Where the datepart parameter is one of the following: year, quarter, month, dayofyear, day, week, hour, minute, second or millisecond. The number parameter is an integer value for the number of dateparts to be added to or subtracted from the date parameter.

Now depending on your needs, your application might use this function to perform mathematical calculations on a given date. So let's come up with a couple of different possible date calculations that an application might use, to demonstrate how to use the DATEADD function.

For my first example, let's assume you have an application that produces invoices to be sent to customers. On the invoice you have two dates, an invoice date, and an invoice due date. The invoice date is the current date, and the invoice due date is calculated by adding 21 days to the invoice date. Below is how you would use the DATEADD function to calculate the invoice due date based on the current date (invoice date).

DECLARE @INVOICE_DATE DATETIME

DECLARE @DUE_DATE DATETIME

SET @INVOICE_DATE = GETDATE()

SET @DUE_DATE = DATEADD(DAY,21,@INVOICE_DATE)

PRINT 'INVOICE DATE: ' + CAST

    (@INVOICE_DATE AS CHAR(11)) + CHAR(13) +  

      'DUE DATE: ' + CAST (@DUE_DATE AS CHAR(11))

For the next example, say you have a car insurance type of application, where policies are renewed every 6 months. If you only store the policy date in your table then you can calculate the policy renewal date by using the DATEADD function. Let's say you have the following policy dates (policy_dt) for each policy (policy_id).

If we use using the "quarter" datepart of the DATEADD function we can easily calculate the 6 month renewal date, by adding 2 "quarter" dateparts to the POLICY_DT column. Here is how we would display all the renewal dates for the above policies:

SELECT POLICY_ID, DATEADD(QUARTER,2,POLICY_DT) RENEWAL_DATE FROM CONTRACTS

DATEDIFF

The DATEDIFF function is used to calculate the number of date and time boundaries crossed between two different dates. This function returns an integer value. A call to the DATEDIFF function takes the following format:

DATEDIFF(datepart, startdate, enddate)

Where datepart is one of the following: year, quarter, month, dayofyear, day, hour, minute, second or millisecond. Startdate and enddate are datetime values for which you want to find the difference.

As stated above, the DATEDIFF function is used to calculate the number of date and time boundaries crossed between two dates. So what value do you think might be returned if you used '12/31/2002 23:59:59.997' for the startdate and '01/01/2003 00:00:00.000' for the enddate, when the datepart is day or year? Clearly, the amount of time between these two dates is only 3 milliseconds. Find out by running the following two SELECT statements.

SELECT DATEDIFF(DAY,'2002-12-31 23:59:59.997',

'2003-01-01 00:00:00.000')

SELECT DATEDIFF(YEAR,'2002-12-31 23:59:59.997',

'2003-01-01 00:00:00.000')

What did you discover? You should have found, that the number of days difference is 1, and the number of years difference is also 1.

Now if you really want to calculate the number of milliseconds between each of these dates then you will need to use the millisecond datapart as the first parameter to the DATEDIFF function, like so:

SELECT DATEDIFF(MS,'2002-12-31 23:59:59.997',

'2003-01-01 00:00:00.000')

Say you want to calculate the number of weeks between two dates, then you would use the following code:

SELECT DATEDIFF(WEEK,'2003-05-24','2003-05-25')

This code returns 1, as the number of weeks between these two dates. Note that using the "SET FIRSTDAY" command does not change the results of using the WEEK datepart. The DATEDIFF function always assumes Sunday is the first day of a week. Now if you run the following, then there would be 0 weeks between these two dates. Since "2003-05-25" is a Sunday, and "2003-05-31" is a Saturday the DATEDIFF function returns a 0, since both theses dates are in the same week.

SELECT DATEDIFF(WEEK,'2003-05-25','2003-05-31')	

A more practical example of how an application might use the DATEDIFF function might be determining the number of days it takes to process an order. The number of days to process an order is calculated by determining the number of days between the date when an order was received and when the actual order was shipped out. Say we have the following ORDERS table, where the ORDER_DT is when the order was received, and the ORDER_SHIP_DT is the actual date when the order was shipped.

Now, to calculate the number of days to process an order with the DATEDIFF function, your SELECT statement would look like this:

SELECT ITEM_ORDERED, DATEDIFF(DAY,ORDER_DT, ORDER_SHIP_DT) AS 

PROCESSING_DAYS FROM ORDERS

When this statement runs against the above ORDERS table, the following output would be displayed.

Note from this output that your management might want to review why every time WIDGIT A was ordered it took 9 days to process the order, although all the rest of the WIDGITS (B, X, and Z) only took 1 day to process the order.

Universal Time

So what is "Universal Time"? Universal Time is the local time at the Greenwich meridian, which is longitude zero, or more historically called "Greenwich Mean Time". "Universal Time" is most often used to record astronomical and weather phenomena. Most civil usages of "Universal Time" are normally referred to as "Coordinated Universal Time," which is normally abbreviated as UTC. UTC time is five hours ahead of Eastern Standard Time.

GETUTCDATE Function

SQL Server provides the GETUTCDATE function to get UTC time. The GETUTCDATE function returns a datetime value that represents the current UTC time. The syntax for the GETUTCDATE function is as follows:

	GETUTCDATE()

The UTC date and time is calculated based on the current time and time zone on the local machine.

If you run the following command on your server, the current UTC date will be displayed.

	SELECT GETUTCDATE()

GETDATE Function

The GETDATE function provides a means to return the local machine time (time for the time zone set on local server) in internal format. The syntax for the GETDATE function is as follows:

	GETDATE()

The GETDATE function is useful to get the current time so you can use it in your application.

Now it is time to show a practical example of how to use both the GETUTCDATE and the GETDATE functions. Let's use these functions along with the DATEDIFF function to calculate the number of hours between a local SQL Server machine and "Coordinated Universal Time". Here some code that does exactly that:

SELECT DATEDIFF(HH,GETUTCDATE(),GETDATE())

Conclusion

I hope this article has shed some light on how to use and what you can do with the DATEADD, and DATEDIFF functions. Also, you should now understand what a UTC date is and how the GETUTCDATE can return a UTC date. In addition, you should also understand how the GETDATE function would return the local server time in internal datetime format.

I hope this article and the first three (part 1, part2, and part3) in this series covered most of what you might need to know to work with all of the different aspects of SQL Server dates.

» See All Articles by Columnist Gregory A. Larsen