471,853 Members | 1,805 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,853 software developers and data experts.

Converting integer to datetime

Hi All,

How do you convert int value to datetime datatype in sql server
e.g 900mins to hh:mm:ss

Regards

Ola
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
3 45485
A datetime has to have a value for both the date and the time. You can use
DATEADD to add X minutes onto a date:

SELECT DATEADD(MI,999,'20000101')

Result:

------------------------------------------------------
2000-01-01 16:39:00.000
If you just want the time without the date then you need to convert it to a
CHAR:

SELECT CONVERT(CHAR(8),DATEADD(MI,999,'20000101'),108)

Result:

--------
16:39:00
--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #2
To follow on, it there a way to convert a date which is stored as an
integer into a normal datetime datatype.

I want to run a crystal report against MSDB to list jobs that have
successfully run (or not). The only item in msdb..sysjobhistory that
is of use to me is run_date but it is stored 20031107 as an integer.
How do I get it to change to a normal datetime?

Thanks

S

P.S. - Why, in msdb, did they not make that column as a datetime...?
Jul 20 '05 #3
DECLARE @intdate INTEGER
SET @intdate = 20031107

SELECT CAST(CAST(@intdate AS VARCHAR) AS DATETIME)

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by js | last post: by
20 posts views Thread by Ollie | last post: by
4 posts views Thread by M. Uppal | last post: by
8 posts views Thread by Mika M | last post: by
18 posts views Thread by Dirk Hagemann | last post: by
NeoPa
reply views Thread by NeoPa | last post: by
aboka
reply views Thread by aboka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.