In the last article of this series, Gregory Larsen discusses how to use the
DATEDIFF and DATEADD functions to perform different date related mathematical
calculations.
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).
POLICY_ID

POLICY_DT

1

20021030

2

20021030

3

20020606

4

20030431

5

20020521

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,'20021231 23:59:59.997',
'20030101 00:00:00.000')
SELECT DATEDIFF(YEAR,'20021231 23:59:59.997',
'20030101 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,'20021231 23:59:59.997',
'20030101 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,'20030524','20030525')

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 "20030525" is a Sunday, and "20030531" is a Saturday the DATEDIFF
function returns a 0, since both theses dates are in the same week.
SELECT DATEDIFF(WEEK,'20030525','20030531')

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.
ID

ORDER_DT

ORDER_SHIP_DT

ITEM_ORDERED

1

20030401

20030410

WIDGIT A

2

20030402

20030403

WIDGIT B

3

20030402

20030411

WIDGIT A

4

20030403

20030404

WIDGIT X

5

20030405

20030414

WIDGIT A

6

20030405

20030406

WIDGIT Z

7

20030407

20030408

WIDGIT B

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.
ITEM_ORDERED 
PROCESSING_DAYS 
WIDGIT A 
9 
WIDGIT B 
1 
WIDGIT A 
9 
WIDGIT X 
1 
WIDGIT A 
9 
WIDGIT Z 
1 
WIDGIT B 
1 
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