SQL help required please !

  • 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

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

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

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

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

  • 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,

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