DATEDIFF question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DATEDIFF question

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2012-06-21 : 13:06:36

Right now I have the following in a stored procedure:

DATEDIFF(DAY, ship_date, p.delivered_date)as [ship_days]

I use this to determine how many days it takes a shipment to reach its destination, but is there any way I can have it ignore weekends? Things aren't delivered on weekends so it adds false days in there.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-21 : 13:36:31

Create a calendar table, populate with dates and a flag to say whether a weekend or not - then join to count
Something like

;with cte as
(
select dte = convert(datetime,'20080101')
union all
select dte = DATEADD(dd,1,dte) from cte where dte < '20201231'
)
insert dim_Calendar
(
dim_Calendar_id,
dte,
WorkingDay = case when datename(dw,dte) in ('Saturday','Sunday') then 0 else 1 end
from cte
option (maxrecursion 0);

select t.orderno, sum(d.workingday)-1
from dim_calendar d
join mytbl t
on d.dte between t.ship_date and t.delivered_date
group by t.OrderNo

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 17:48:22

DATEDIFF(DAY, ship_date, p.delivered_date) - ((DATEDIFF(wk,ship_date, p.delivered_date)-1) * 2)as [ship_days]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-22 : 07:20:11

quote:
Originally posted by visakh16

DATEDIFF(DAY, ship_date, p.delivered_date) - ((DATEDIFF(wk,ship_date, p.delivered_date)-1) * 2)as [ship_days]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think I vaguely understand the logic you are trying to implement - count the total number of days, and subtract twice the number of week boundary crossings. But, I am not able to get the right answer when I try - for example this gives me 5 days.

DECLARE @ship_date       date,
        @delivered_date  date;
SET @ship_date = '20120101';
SET @delivered_date = '20120104';

SELECT
	DATEDIFF(DAY, @ship_date, @delivered_date) -((DATEDIFF(wk, @ship_date, @delivered_date) -1) * 2) AS 
	[ship_days]

Does it depend on the @@DATEFIRST setting? My @@DATEFIRST is 7.

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-06-22 : 07:49:14

DECLARE @ship_date date,
@delivered_date date;
SET @ship_date = '20120601';
SET @delivered_date = '20120604';

select
DATEDIFF(DAY, @ship_date, @delivered_date) -(DATEDIFF(wk, @ship_date, @delivered_date)*2)

meef
Posting Yak Master

113 Posts

Posted - 2012-06-22 : 08:23:45

Thanks guys, the one below seems to work:

quote:
Originally posted by subhaoviya

DECLARE @ship_date date,
@delivered_date date;
SET @ship_date = '20120601';
SET @delivered_date = '20120604';

select
DATEDIFF(DAY, @ship_date, @delivered_date) -(DATEDIFF(wk, @ship_date, @delivered_date)*2)

I have a ship date of 6/14 and a delivery of 6/21 and it's coming up 5 days as it should, not 7.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-22 : 08:43:52

Thanks subhaoviya, that works. Visakh was almost right, he threw in an extra minus one probably to see if we are really paying attention

Intuitively, I would have thought that the week boundary would depend on the DATEFIRST setting, but seems like it does not. If I had to bet, I would have bet on it being a bug, but it is a feature! See here: http://www.sqlmag.com/article/sql-server/datediff-with-the-week-part

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-22 : 08:59:03

What about bank holidays?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-22 : 10:56:18

quote:
Originally posted by nigelrivett

What about bank holidays?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Joe Celko and others have convinced me that if you want to consider anything beyond Saturday & Sunday as holidays, the only sane way is to use a calendar table. http://www.simple-talk.com/sql/t-sql-programming/temporal-data-techniques-in-sql-/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 22:28:03

quote:
Originally posted by sunitabeck

Thanks subhaoviya, that works. Visakh was almost right, he threw in an extra minus one probably to see if we are really paying attention

Intuitively, I would have thought that the week boundary would depend on the DATEFIRST setting, but seems like it does not. If I had to bet, I would have bet on it being a bug, but it is a feature! See here: http://www.sqlmag.com/article/sql-server/datediff-with-the-week-part

Oops...seems like I complicated it a bit..

Didnt test it properly as I was in between something
Nice catch

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources