Post reply

Append a Time to an Existing Date

  • February 13, 2014 at 3:54 pm

    #283709

    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

  • February 13, 2014 at 3:59 pm

    #1688939

    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;

  • February 13, 2014 at 4:04 pm

    #1688942

    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