Converting integer to datetime

[ola Alli]

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!

[David Portas]

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
--

[Simon]

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...?

[David Portas]

DECLARE @intdate INTEGER
SET @intdate = 20031107

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

--
David Portas
------------
Please reply only to the newsgroup
--