Home Forums SQL Server 2008 SQL Server 2008 - General how to combine and convert two integer columns to datetime RE: how to combine and convert two integer columns to datetime

  • March 15, 2013 at 3:06 pm

    #1597709

    naveen.pasupuleti (3/15/2013)

    Thanks Lynn for your response.

    The code you shared works for this example but sometimes integer time column or col B can have values like 93000 (9.30am) or 0 (12 am). The number of digits vary in the column.

    SELECT last_run_date,last_run_time from msdb.dbo.sysjobservers

    One closest example I can think of is from msdb database. Sysjobservers table has two columns that match exactly my requirement. Last_run_date column is a integer type having date number and Last_run_time is a integer type having time number. How to get datetime out of these two columns.

    Try this:

    declare @TestDate int = 20130314,

    @TestTIme int = 123000;

    select cast(cast(@TestDate as varchar(8)) as datetime),

    cast(stuff(stuff(cast(@TestTime as varchar(6)),5,0,':'),3,0,':') as time(0)),

    cast(cast(@TestDate as varchar(8)) as datetime) + cast(stuff(stuff(right('000000' + cast(@TestTime as varchar(6)),6),5,0,':'),3,0,':') as time(0));