Posted by Erland Sommarskog on 10/02/76 11:46

smileydip (dipeshn.shah@gmail.com) writes:
> Hi Guys, Could some help me....
> I am trying to use the below specified two field from "SysJobHistory"
> 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.

select convert(datetime, run_date + ' ' +
substring(run_time, 2, 2) + ':' +
substring(run_time, 4, 2) + ':' +
substring(run_time, 6, 2))
from (select run_date = ltrim(str(run_date)),
run_time = ltrim(str(1000000 + run_time))
from msdb..sysjobhistory) 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@sommarskog.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

• Next in forum: Transactional Replication - Modifying Target Tables
• Prev in forum: Converting Int value to datetime
• Thread view: Re: Converting Int value to datetime

[Reply to this message]