May 9, 2016 at 1:56 pm
Hello Everyone,
I have two columns, DateTime and Hours in (HH:MM) as shown below.
I'm trying to add the Hours and Minutes column to the date and time column to get the final date and time.
Data Query
WITH SampleData (STARTDATETIME,HOURS) AS
(
SELECT '2016-04-24 02:30:00.000','03:30'UNION ALL
SELECT '2016-04-25 05:00:00.000','04:00'
)
SELECT *
FROM SampleData
ORDER BY 1,2
Current Results
STARTDATETIME HOURS
2016-04-24 02:30:00.00003:30
2016-04-25 05:30:00.00004:00
Current Results
STARTDATETIME HOURS ENDDATETIME
2016-04-24 02:30:00.00003:30 2016-04-24 06:00:00.000
2016-04-25 05:30:00.00004:00 2016-04-25 09:30:00.000
May 9, 2016 at 2:06 pm
it depends on whether the data is stored as varchars or proper datetime/time data types.
the datetime datatype allsowds you to add to it.
if the columns are datetime, the CTE is just to prove the data type
WITH SampleData (STARTDATETIME,HOURS) AS
(
SELECT convert(datetime,'2016-04-24 02:30:00.000'),convert(time,'03:30') UNION ALL
SELECT '2016-04-25 05:00:00.000','04:00'
)
SELECT *,STARTDATETIME + HOURS As EndDateTime
FROM SampleData
ORDER BY 1,2
if the data is stored as varchars, you'll have to convert them
WITH SampleData (STARTDATETIME,HOURS) AS
(
SELECT '2016-04-24 02:30:00.000','03:30' UNION ALL
SELECT '2016-04-25 05:00:00.000','04:00'
)
SELECT *,convert(datetime,STARTDATETIME) + convert(time,HOURS) As EndDateTime
FROM SampleData
ORDER BY 1,2
Lowell
May 9, 2016 at 2:08 pm
Without knowing the data type of the hours column, I gave this a try:
create table #timestuff (starttime datetime
, addtime time)
insert into #timestuff values ('2016-04-24 02:30:00.000','03:30')
,('2016-04-25 05:00:00.000','04:00')
select starttime
, addtime
, (starttime + addtime) as [AddedTime]
from #timestuff
which did pop out the results you're looking for. If your hours is a character type, you'll need to convert it.
(Ninja'd by Lowell!)
May 9, 2016 at 2:24 pm
Hi There,
My mistake, I just went back and checked, the hours are like '3.5' and '4' instead of 03:30 and 04:00 as initially stated.
So when I ran the
convert(datetime,STARTDATETIME) + convert(time,HOURS) against the actual database I get the following error:
Msg 529, Level 16, State 2, Line 152
Explicit conversion from data type float to time is not allowed.
The hours column is using the following formatting where the time is given in seconds and we are having to convert it into hours.
(CAST(TIMEINSECONDS AS float)/3600) HOURS
Sorry about that. :w00t:
May 9, 2016 at 2:57 pm
DiabloSlayer (5/9/2016)
Hi There,My mistake, I just went back and checked, the hours are like '3.5' and '4' instead of 03:30 and 04:00 as initially stated.
So when I ran the
convert(datetime,STARTDATETIME) + convert(time,HOURS) against the actual database I get the following error:
Msg 529, Level 16, State 2, Line 152
Explicit conversion from data type float to time is not allowed.
The hours column is using the following formatting where the time is given in seconds and we are having to convert it into hours.
(CAST(TIMEINSECONDS AS float)/3600) HOURS
Sorry about that. :w00t:
if your source time is still available in seconds as per above then would just be a case of dateadd(second, time_in_seconds, convert(datetime, STARTDATETIME))
or alternatively
dateadd(second, HOURS * 3600, convert(datetime, STARTDATETIME))
May 9, 2016 at 3:07 pm
Okay, so I was able to resolve this issue by changing the formatting to getting the hours from the minutes column by using the following:
CONVERT(varchar, DATEADD(ms, TIMEINSECONDS * 1000, 0), 114) HOURS instead of the (CAST(TIMEINSECONDS AS float)/3600) HOURS
then I used convert(datetimeSTARTDATETIME) + convert(time,HOURS) to ENDDATETIME.
So THANK YOU !!!
Now, I have another issue.
I have two dates and I want to get the difference in HOURS and MINUTES between these two dates
Date1 = 2016-04-24 02:30:00.000
Date2 = 2016-04-24 06:00:00.000
When I use the following query:
DATEDIFF(HOUR, CONVERT(VARCHAR(5), Date2, 108),CONVERT(VARCHAR(5), Date1, 108))
I'm getting '4' as the answer whereas I'm looking for 3.5 as the final answer.
Can you please help?
Thank you again,
May 9, 2016 at 3:35 pm
Datediff returns the number of whole intervals between the two dates, it will never return a fraction.
Try taking the difference in minutes instead of hours and then dividing by 60.0.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2016 at 3:41 pm
Hi There,
That's exactly what I ended up doing and now I'm getting the accurate results 🙂
THANK YOU ALL for ALL your help !!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply