Date diff

[Erics44]

Hi
I need to find the difference between 2 dates in hours excluding weekends.

Is there a function that will calculate tis for me?
Thanks

 

[SQLDenis]

lookup datediff and datepart in BOL

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[Erics44]

I have, but how do i combine the two?

i can find the number of hours between 2 dates but not excluding weekends without writing an additional function

any ideas?

 

[jnavarro]

You will have to use a case statement in order to determine if the date is a weekend.

i.e.
CASE WHEN Weekday(date1) = 1 THEN 'do something
CASE WHEN Weekday(date1) = 7 THEN 'do something
CASE ELSE 'do something.

 

[Erics44]

Thats great thanks

 

[SQLDenis]

Of course there is more to it, what if the startdate is a Thursday and the end day a Wednesday, or what if it spans more that 1 week, what if there is a holiday in between?

You could create a day table with valid days only and join on that table and have an additional field with 8 hours in it (or 4 hours for half days)
This way you can sum on that field, just a suggestion






“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[Erics44]

I do have a bank holidays table, but I dunno whether or not to use it yet, there are about a million records in the main table and the more calculations i do on it the longer it takes to process