Append a Time to an Existing Date

  • Hello Everyone

    I hope that your day is going a bit better than mine 🙂

    I am working on some really old and funky data. I have a valid date in a column, but the time is stored in the next column, and is stored as an integer. The integer represents the number of seconds past midnight from the date in the datetime column.

    I need some assistance with two things actually. How do I convert the integer into a valid time of day? And How can I append that time to the date in the ApptDate column?

    DECLARE @DateAndTime TABLE

    (

    ApptDate datetime

    , ApptTime int

    )

    INSERT INTO @DateAndTime

    SELECT '2014-01-10 00:00:00.000','68700' UNION ALL

    SELECT '2014-04-04 00:00:00.000','34200' UNION ALL

    SELECT '2014-01-15 00:00:00.000','37800' UNION ALL

    SELECT '2014-01-10 00:00:00.000','46800' UNION ALL

    SELECT '2014-02-03 00:00:00.000','29700' UNION ALL

    SELECT '2014-01-17 00:00:00.000','33300' UNION ALL

    SELECT '2014-01-08 00:00:00.000','40500' UNION ALL

    SELECT '2014-01-08 00:00:00.000','31500' UNION ALL

    SELECT '2014-01-16 00:00:00.000','53100' UNION ALL

    SELECT '2014-01-08 00:00:00.000','59400'

    SELECT * FROM @DateAndTime

    I have never tried either, but everything that I can come up with is not correct.

    Thank you in advance, and I greatly appreciate your time, assistance, comments and code suggestions.

    Andrew SQLDBA

  • Really simple actually:

    DECLARE @DateAndTime TABLE

    (

    ApptDate datetime

    , ApptTime int

    )

    INSERT INTO @DateAndTime

    SELECT '2014-01-10 00:00:00.000','68700' UNION ALL

    SELECT '2014-04-04 00:00:00.000','34200' UNION ALL

    SELECT '2014-01-15 00:00:00.000','37800' UNION ALL

    SELECT '2014-01-10 00:00:00.000','46800' UNION ALL

    SELECT '2014-02-03 00:00:00.000','29700' UNION ALL

    SELECT '2014-01-17 00:00:00.000','33300' UNION ALL

    SELECT '2014-01-08 00:00:00.000','40500' UNION ALL

    SELECT '2014-01-08 00:00:00.000','31500' UNION ALL

    SELECT '2014-01-16 00:00:00.000','53100' UNION ALL

    SELECT '2014-01-08 00:00:00.000','59400'

    SELECT * FROM @DateAndTime

    update @DateAndTime set

    ApptDate = dateadd(ss, ApptTime, ApptDate);

    select * from @DateAndTime;

  • Lynn

    Thank You so Very much. I was trying to make this so much more difficult.

    I am so embarrassed, I am not going to show or tell what path I was going down.

    Thanks again

    Andrew

Viewing 3 posts - 1 through 2 (of 2 total)

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