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