Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 3, 2003

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

By Gregory A. Larsen

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.

ID

ORDER_DT

ORDER_SHIP_DT

ITEM_ORDERED

1

2003-04-01

2003-04-10

WIDGIT A

2

2003-04-02

2003-04-03

WIDGIT B

3

2003-04-02

2003-04-11

WIDGIT A

4

2003-04-03

2003-04-04

WIDGIT X

5

2003-04-05

2003-04-14

WIDGIT A

6

2003-04-05

2003-04-06

WIDGIT Z

7

2003-04-07

2003-04-08

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM