datediff part2

Question

Post reply

datediff part2

Pedro Reis-267786

Mr or Mrs. 500

Points: 590
More actions
October 25, 2006 at 8:00 am

#170757

I think there no easy way ta calculate this, but anyway...can you give me some clues to do hit ….

I need to calculate the minutes between 2 dates base on a interval (like 9H00AM to 13H00PM and 14H30PM to 18H30PM),     7,5H of work/days,     on week days ….

For example
if date1= ‘2006-10-25 10:00AM’ and date2=’ 2006-10-25 15:00PM’     -> time = 3H30 ( 210 min)
if date1= ‘2006-10-25 18:00 PM’ and date2=’ 2006-10-26 18:00PM’     -> time = 7H30 ( 450 min)

Note: I cannot use SP to do this

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

I cannot use SP to do this

Why not?

Look up DATEDIFF in the Books OnLine. Get the results in seconds and then change the seconds into the result you want.

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 2

BTW-you can put this in a Stored Procedure.

DECLARE @mytime INT

DECLARE @myhour INT

DECLARE @mymin INT

DECLARE @mysec INT

SET @mytime = (SELECT DATEDIFF(ss, , ))

SET @myhour = (SELECT @mytime/3600)

SET @mytime = (SELECT @mytime%3600)

SET @mymin = (SELECT @mytime/60)

SET @mysec = (SELECT @mysec%60)

SELECT CAST(@myhour AS VARCHAR(2)) + 'H' + CAST(@mymin AS VARCHAR(2)) + 'M' + CAST(@mysec AS VARCHAR(2)) + 'S'

-SQLBill

DaveItz SSCrazy Points: 2146 More actions · Answer 3

I just threw this together so I'm not sure how sound the logic is but you can make a UDF out of the following code (w/ a weekend skipping parameter):

declare @query_range_start datetime, @query_range_end datetime, @query_range_days int
declare @query_range_start_no int, @query_range_end_no int, @query_range_length int

declare @period1_range_starttime datetime, @period1_range_endtime datetime
declare @period1_range_start_no int, @period1_range_end_no int, @period1_length bigint

select @period1_range_starttime = '9:00', @period1_range_endtime = '12:59:59'
-- select @period1_range_starttime = '14:30', @period1_range_endtime = '18:29:59'

if (@period1_range_starttime > @period1_range_endtime)
   select @period1_range_endtime = @period1_range_endtime + 1
select @period1_range_start_no = (datepart(hh, @period1_range_starttime) * 60 * 60) 
                               + (datepart(n, @period1_range_starttime) * 60)
                               + (datepart(s, @period1_range_starttime)),
       @period1_range_end_no = (datepart(hh, @period1_range_endtime) * 60 * 60)
                             + (datepart(n, @period1_range_endtime) * 60)
                             + (datepart(s, @period1_range_endtime))
select @period1_length = @period1_range_end_no - @period1_range_start_no + 1
select @period1_range_starttime, @period1_range_endtime, @period1_range_start_no, @period1_range_end_no, @period1_length

select @query_range_start = '2006-10-25 10:00', @query_range_end = '2006-10-25 14:59:59'
--select @query_range_start = '2006-10-25 18:00', @query_range_end = '2006-10-26 17:59:59'
select @query_range_days = datediff(d, convert(datetime, convert(varchar, @query_range_start, 101)), convert(datetime, convert(varchar, @query_range_end, 101)))
select @query_range_start_no = (datepart(hh, @query_range_start) * 60 * 60)
                             + (datepart(n, @query_range_start) * 60)
                             + (datepart(s, @query_range_start)),
       @query_range_end_no = (datepart(hh, @query_range_end) * 60 * 60)
                           + (datepart(n, @query_range_end) * 60)
                           + (datepart(s, @query_range_end))

select @query_range_length = ((@query_range_days+1) * @period1_length) /* ADD CODE TO ONLY CALCULATE WEEKDAYS */
                           /* ADD CODE HERE:
                              IF FirstDayIsAWeekend subtract @period1_length
                              Otherwise... */
                           - case when @query_range_start_no >= @period1_range_end_no 
                                      then @period1_length
                                  when @query_range_start_no between @period1_range_start_no and @period1_range_end_no
                                      then @query_range_start_no - @period1_range_start_no
                                  else 0
                             end
                           /* ADD CODE HERE:
                              IF LastDayIsAWeekend 
                                 if last day is the same day as the first day subtract 0
                                 otherwise subtract @period1_length
                              Otherwise... */
                           - case when @query_range_end_no <= @period1_range_start_no
                                      then @period1_length
                                  when @query_range_end_no between @period1_range_start_no and @period1_range_end_no
                                      then @period1_range_end_no - @query_range_end_no
                                  else 0
                             end
select @query_range_start, @query_range_end, @query_range_start_no, @query_range_end_no, @query_range_length

And after you total the two calls to the UDF you can use SQLBill's code:

DECLARE @mytime INT

DECLARE @myhour INT

DECLARE @mymin INT

DECLARE @mysec INT

SET @mytime = @query_range_length

SET @myhour = (SELECT @mytime/3600)

SET @mytime = (SELECT @mytime%3600)

SET @mymin = (SELECT @mytime/60)

SET @mysec = (SELECT @mytime%60) /* <-- Typo */

SELECT CAST(@myhour AS VARCHAR(2)) + 'H' + CAST(@mymin AS VARCHAR(2)) + 'M' + CAST(@mysec AS VARCHAR(2)) + 'S'

You probably should change things to use the bigint datatype. I can see the seconds getting out of hand