Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Please help me (Date Difference Issue) Expand / Collapse
Author
Message
Posted Thursday, May 17, 2012 12:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746
mota7128 (5/17/2012)
Hi Guys,

I am using SQL 2008 R2, and I do thank all of you guys, REALLY love you.

Much thanks for you "Lynn Pettis", your solution is awesome for me and I think that I can amend it to exclude Saturday and Sunday

Best Regards,
Hatem


Look close, I did that already (hopefully) in the code I just posted. Be sure to test it in your environment.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1302063
Posted Thursday, May 17, 2012 3:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 25, 2014 7:16 AM
Points: 247, Visits: 421
Sorry, just got back. I didn't really read through your second post, but I think this is right/close/gives you an idea. Also, SQL 2008 R2 makes it easier.

DECLARE	@timeend TIME = '17:00'
DECLARE @timestart TIME = '08:30'
DECLARE @daylength INT = DATEDIFF(mi, @timestart, @timeend)
DECLARE @StartDate DATETIME= '2012-05-10 8:30:00.000';
DECLARE @EndDate DATETIME= '2012-05-14 15:00:00.000';

SELECT
(DATEDIFF(d, @StartDate, @enddate) * @daylength) --TotalMinutes,
- (DATEDIFF(week, @StartDate, @enddate) * 2 * @daylength) --Weekend
- (@daylength - DATEDIFF(mi, @timestart, CAST(@EndDate AS TIME))) --LastDay
- DATEDIFF(mi,@timeend, CAST(@startdate AS TIME) ) -- First Day

Always remember to test.



Post #1302167
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse