By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

Converting Int value to datetime

P: n/a
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.

Regards

Dipesh Shah

May 2 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
smileydip (di**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 2 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.