Solved

# Datediff

Posted on 2008-10-17
466 Views
How can I use sql to give me the difference between a date timestamp field in days and exclude weekends?  The data below shows how many times an item has changed status, I want to create an extra column called 'Days' to show the number of days that the item has changed status between each line.  If this item does not change status, then I want it to calculate against the current date. Please see an example of the data below:

Change Date

2008-08-13 12:16:56.377
2008-08-13 12:16:56.377
2008-08-13 19:08:39.090
2008-08-14 21:15:28.617
2008-08-24 18:57:13.433
2008-09-05 16:33:02.653
2008-09-05 16:33:02.653
2008-09-25 12:56:46.277
2008-09-30 15:42:14.880
2008-10-01 15:42:40.640
2008-10-03 15:51:36.587
2008-10-17 09:49:18.010

This is what I would like for my output to be:

Change Date                                                          Days

2008-08-13 12:16:56.377                                         0
2008-08-13 12:16:56.377                                         0
2008-08-13 19:08:39.090                                         0
2008-08-14 21:15:28.617                                         1
2008-08-24 18:57:13.433                                        10
2008-09-05 16:33:02.653                                        12
2008-09-05 16:33:02.653                                        12
2008-09-25 12:56:46.277                                        20
2008-09-30 15:42:14.880                                         5
2008-10-01 15:42:40.640                                         1
2008-10-03 15:51:36.587                                         2
2008-10-17 09:49:18.010                                         14

0
Question by:daintysally
• 3

LVL 37

Accepted Solution

momi_sabag earned 500 total points
this will give you the result including weekends

with a as
(select change_date, row_numbeR() over (order by change_date) as rown
from your_table
)

select  t1.change_date,
case  when t2.rown is null then datediff(d, t1.change_date, getdate())
else datediff(d, t1.change_date, t2.change_date)

from  a t1 left outer join a t2 on t1.rown = t2.rown-1
0

LVL 37

Expert Comment

if you want to remove the weekends you need to add in the case checks for the t1.change_date and t2.change_date day of the week and according to the dayof the week (which can be retreived using datepart) you can calculate how many weekend days you had in there
(it depends on the first day of the week and which days are considerd weekends for you)
0

Author Comment

I do not understand your solution.  Can you please explain further for me?
0

LVL 37

Expert Comment

the code i wrote sums up all the days
removing the weekends depends on where you from
some countries week end is friday-saturday, some weekend is saturday-sunday etc...
you need to modify the query and add logic that will calculate the number of weekend days between the dates
0

## Featured Post

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…