Converting Int value to datetime

[smileydip]

Hi Guys, Could some help me....
I am trying to use the below specified two field from "SysJobHist ory"
table from "MSDB". Field name "run_date" and "run_time". Both this
fields are of INT data type. Data saved in the above two fields is in
this this format 20060501 and 204001 respectively. I want save the data
from this two field in another table in date time format. I want to
write a SELECT statement which converts the data from this two fields
into date time format so that it could be saved in another table which
has date time data type column respectively.

Regards

Dipesh Shah

[Erland Sommarskog]

Re: Converting Int value to datetime

smileydip (dipeshn.shah@g mail.com) writes:[color=blue]
> Hi Guys, Could some help me....
> I am trying to use the below specified two field from "SysJobHist ory"
> table from "MSDB". Field name "run_date" and "run_time". Both this
> fields are of INT data type. Data saved in the above two fields is in
> this this format 20060501 and 204001 respectively. I want save the data
> from this two field in another table in date time format. I want to
> write a SELECT statement which converts the data from this two fields
> into date time format so that it could be saved in another table which
> has date time data type column respectively.[/color]

select convert(datetim e, run_date + ' ' +
substring(run_t ime, 2, 2) + ':' +
substring(run_t ime, 4, 2) + ':' +
substring(run_t ime, 6, 2))
from (select run_date = ltrim(str(run_d ate)),
run_time = ltrim(str(10000 00 + run_time))
from msdb..sysjobhis tory) AS s

That is, convert first to string. To retain leading zeroes, I simply added
1000000 to run_time.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx