Working with SQL Server Date/Time Variables: Part Four - Date Math and Universal Time

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