ROUNDING TIME EXPRESSION?? 1

[DOGWATCH]

HI!

I am writing a rather lengthy procedure that generates a time sheet report, calculating, Lunch, regular and overtime.

Here is a snipet
Lunch=DateDiff(Mi,Cast(StartLunch as DateTime),Cast(EndLunch as DateTime))

However I need to format this time so that it rounds up to the nearest 1/4 hour. If it returns 8:35AM it has to return 8:45AM if its 8:20AM it rounds to 8:30AM.

I just need an expression that will format the time field to round up to the nearest 1/4 hour.

 

[DOGWATCH]

ooops got cut off there. Anyway, any help with this conversion will be greatly appreciated.

Thanks!
-ITSN DOGWATCH USNR

 

[ArtieChoke]

You'll have to extract the hour and minute values using datepart("mm",date) and datepart("hh",date).

To round to the nearest 15, use this calc:

(datepart("mm",date)+ 14)/15*15

you'll have to check when it goes to 60 so you can reset to zero and up the hour by 1.

"I think we're all Bozos on this bus!

 

[DOGWATCH]

Ok I am just not sure how to fit that into this expression. here is the code:

Regular = IsNull(	Case When Cast(Duration as DateTime)< Cast(Lunch as DateTime) Then '0:00' Else
			Case When DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))>480 
			Then '8:00' 
			Else Cast(DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))/60 as varchar(10)) + ':' +
			Case When  (DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))%60)<10 Then 
			'0' + Cast(DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))%60 as Varchar(10)) Else
			Cast(DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))%60 as Varchar(10))End End End,'*')

I am just not too clear how to format that expression into the 15 minute interval requirement as mentioned. Any ideas?

 

[ArtieChoke]

Is this in a cursor loop or a select statement? If it's within a cursor, then you could write a stored procedure or in-line calc to handle it. Because you want it rounded up, anything past :45 will have to adjust the hour and it could be done, but it will be ugly!

Another option would be to build a user-defined function. Check out the T-SQL help - that might be the best route to go.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[gavinhuet]

Try this, you could put it in a function which accepts the time input and return the time to the next 15 minutes...

DECLARE @myDate SMALLDATETIME
SET @myDate = '12 march 2004 12:46'
SET @myDate = DATEADD(MINUTE, (DATEPART(MINUTE, @myDate) + 14) / 15 * 15 - DATEPART(MINUTE,@myDate), @myDate)
SELECT @myDate

Regards
-- Gavin

 

[DOGWATCH]

Am I not thinking this through right Your adding 14 based on the fixed time of 12:46?? 12:46 + 14 minutes = 1:00

SET @myDate = DATEADD(MINUTE, (DATEPART(MINUTE, @myDate) + 14) / 15 * 15 - DATEPART(MINUTE,@myDate), @myDate)

Seems to me that I will need to grab the minutes then if its under 15 minutes we take 15 minus minutes and format that into a variable then use then plug that back into the equation.

We would have the a separate case for every 15 minute interval and if its above 45 minutes then it needs to also increment the hour.

Am I right here or wil that original equation work for everything??

 

[ArtieChoke]

Dogwatch, you are correct. This equation:

SET @myDate = DATEADD(MINUTE, (DATEPART(MINUTE, @myDate) + 14) / 15 * 15 - DATEPART(MINUTE,@myDate), @myDate)

will only work on a date, not a minute value.

If I understand correctly, you are trying to format a time value in minutes to display as hours:minutes with rounding at 15 minute intervals.

So 130 minutes would be 2:15. There is no date info involved.

I still think the best way is to create a function that will return the hour:minute format the way you want it. That way, you can drop 3 of the case statements - or even put the entire logic in the function and you wouldn't need the case statements at all.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[DOGWATCH]

Good news: Created a nice little stored procedure to convert input time into a perfectly formatted string returning the rounded value to the nearest 1/4 hour

Bad news: Have to work with a complicated nested select statement to pass the values in:

Not sure how to isolate the computed time of Regular and OT (Overtime) to my Stored Proc. any suggestions??


Select Regular = IsNull(Case When Cast(Duration as DateTime)< Cast(Lunch as DateTime) Then '0:00' Else Case When DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))>480 Then '8:00' Else Cast(DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))/60 as varchar(10)) + ':' + Case When (DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))%60)<10 Then '0' + Cast(DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))%60 as Varchar(10)) Else Cast(DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))%60 as Varchar(10))End End End,'*')

,OT= IsNull(Case When DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))>480 Then
Cast((DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))-480)/60 as varchar(10)) + ':' +
Case When ((DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))-480)%60) < 10 Then
'0' + Cast((DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))-480)%60 as varchar(10)) Else
Cast((DateDiff(Mi,Cast(Lunch as DateTime),Cast(Duration as DateTime))-480)%60 as varchar(10)) End
End,'')
From
(
Select StartDay,StartLunch,EndLunch,EndDay