469,148 Members | 1,539 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,148 developers. It's quick & easy.

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 45319
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
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.