locked
combining datetime and time RRS feed

  • Question

  • Hi,

    I have two columns Datetime and Time as below:

    2010-10-03 00:00:00.000             1739

    I want to combine them in one field, please note that time is  1739 which 5:39 pm.

    Thank you

     


    Tuesday, August 23, 2011 4:30 PM

Answers

  • Just use cast and dateadd -- and possible convert if you want to format it:

    declare @test table
    ( a datetime, b integer )
    insert into @test
    select '20101003 00:00:00.000', 1739
    
    select
     a,
     b,
     dateadd(mi, 60 * (b/100) + b%100, a
     ) as Combined,
     convert(varchar(16), dateadd(mi, 60 * (b/100) + b%100, a), 120
     ) as Formatted
    from @test
    /* -------- Output: --------
    a            b      Combined        Formatted
    ----------------------- ----------- ----------------------- ----------------
    2010-10-03 00:00:00.000 1739    2010-10-03 17:39:00.000 2010-10-03 17:39
    */
    
    

     

    • Proposed as answer by Naomi N Tuesday, August 23, 2011 4:44 PM
    • Marked as answer by Farrukh Lala Tuesday, August 23, 2011 5:27 PM
    Tuesday, August 23, 2011 4:36 PM

All replies

  • Just use cast and dateadd -- and possible convert if you want to format it:

    declare @test table
    ( a datetime, b integer )
    insert into @test
    select '20101003 00:00:00.000', 1739
    
    select
     a,
     b,
     dateadd(mi, 60 * (b/100) + b%100, a
     ) as Combined,
     convert(varchar(16), dateadd(mi, 60 * (b/100) + b%100, a), 120
     ) as Formatted
    from @test
    /* -------- Output: --------
    a            b      Combined        Formatted
    ----------------------- ----------- ----------------------- ----------------
    2010-10-03 00:00:00.000 1739    2010-10-03 17:39:00.000 2010-10-03 17:39
    */
    
    

     

    • Proposed as answer by Naomi N Tuesday, August 23, 2011 4:44 PM
    • Marked as answer by Farrukh Lala Tuesday, August 23, 2011 5:27 PM
    Tuesday, August 23, 2011 4:36 PM
  • Try:

    declare @t table (dt Datetime, tm int)
    insert into @t values
    ('2010-10-03 00:00:00.000',       1739),
    ('2010-10-03 00:00:00.000',       339)
    select convert(datetime,CONVERT(char(10),dt,120) + ' ' + stuff(RIGHT('0' + convert(varchar(4),tm),4),3,0,':') + ':00')
    from @t 
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, August 23, 2011 4:42 PM