asked on

Convert Hours to Date Range

I am looking for way to convert hours to a date range via transact sql.

Assumption: The date range would always start on the 1st of the current month.

For example:
If I give a value of 48 hrs.
Then the result should be 7/1/2009 12:00:00AM - 7/2/2009 23:59:59 PM

Also, the input value of hours could also be in fractions, such as 48.15 hrs.

Thanks

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

Hi, try this small enhancement to mwvisa1's approach , and assuming that the 15 in 48.15 is 15 minutes

we also want to subtract - 0.00000005 of a day (giving the .003 rounding error in the datetime)

DECLARE @hours DECIMAL(10,2)
SET @hours = 48;

select dateadd(n,(@hours%24.0)*100,DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)+convert(int,@hours/24)) - 0.00000005
GO

DECLARE @hours DECIMAL(10,2)
SET @hours = 48.15;

select dateadd(n,(@hours%24.0)*100,DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)+convert(int,@hours/24)) - 0.00000005

Mark Wills

Hi,

Not really one to complain, but for 48 hours, thought you wanted the result to be 7/2/2009 23:59:59 PM ?

Now, I might be wrong about the 15 being 15 minutes, but if we look at the 48 hours part then:

DECLARE @hours DECIMAL(10,2)
SET @hours = 48;

select dateadd(n,(@hours%24.0)*100,DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)+convert(int,@hours/24)) - 0.00000005 -- gives answer 7/2/2009 23:59:59 PM

SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) + (@hours / 24.0) AS endDt -- gives answer 7/3/2009 00:00:00

So... Think there is something wrong here. Either / Both mwvisa1 and I would have been more than happy to have finished the actual answer, and you never got back about the minutes - do you really have "hours" as say 48.90 and that actually represents 48 hours plus 90% of an hour ?

Kevin Cross

You are correct, Mark.

I was expected the dates to be used as such.

aDate >= DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)
and aDate < DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) + (@hours / 24.0)

If needs to be exact as Mark suggested, then just adjust the selected solution with Mark's subtraction of nanoseconds.

DECLARE @hours DECIMAL(10,2)
SET @hours = 48;
SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) AS startDt
, DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) + ((@hours - 0.0000005)/24.0) AS endDt

Open in new window

Mark Wills

Hi mwvisa1,

It wsn't a comment directed at you or your code. In fact I was supporting your code from the get go posting my entry as an enhancement to yours (hence no need for start date).

Recently, I have learned that it is best to say something when one is passionate about a subject, and wanted to clarify the Askers actions.

More importantly was the concern about minutes. The example would have been better if it showed 48.75 being 48 hours and three quarters if using a decimal hour system (some timesheets / job card type systems use the decimal to denote minutes).

Cheers,

Mark