Convert Hours to Date Range

baldae
baldae used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Most Valuable Expert 2011
Commented:
baldae

Something like this could get you the date range.

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

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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 WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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 CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
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 WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial