SQL help required please !

Question

Post reply

SQL help required please !

DiabloSlayer

SSCrazy

Points: 2232
More actions
May 9, 2016 at 1:56 pm

#310471

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

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jasona.work SSC Guru Points: 50080 More actions · Answer 2

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!)

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 3

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:

frederico_fonseca SSCoach Points: 16004 More actions · Answer 4

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))

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 5

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,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DiabloSlayer SSCrazy Points: 2232 More actions · Answer 7

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 !!