Post reply

convert date as int to date

  • May 15, 2008 at 6:22 am

    #129076

    Hello,

    I've a have 2 fields, 1 with a date and 1 with a time, the date and time are stored in a field with datatype integer like this:

    9:10:24 is stored as : 91024

    21/05/2008 is stored as 20080521

    Now I want to do some functions on it (add a few days, hours,..) but how can I convert them to a "data time" datatype ?

    kind regards,

    steve

  • May 15, 2008 at 6:35 am

    #815818

    I've found the solution @ http://www.sqldev.net/sqlagent/SQLAgentDateTime.htm

    Thanks!

  • May 15, 2008 at 6:54 am

    #815831

    Hi

    i guess this will help you.

    Use this as example

    SELECT server as 'Server', [name] as 'Job Name', CONVERT(datetime, SUBSTRING(CAST(run_date as varchar(8)),1,4) + '-' + SUBSTRING(CAST(run_date as varchar(8)),5,2) + '-' + SUBSTRING(CAST(run_date as varchar(8)),7,2) + ' ' + SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(8)),6),1,2) + ':' + SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(8)),6),3,2) + ':' + SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(8)),6),5,2) ) as RunDateTime,message as 'Error Message'INTO ##tmp1FROM msdb..sysjobhistory jh join msdb..sysjobs j ON jh.job_id=j.job_id

    Thanks

    jaypee

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

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