Datetime format from Int - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Datetime format from Int

Author

Topic

fvinagre
Starting Member

29 Posts

Posted - 2004-11-15 : 05:11:53

Hi Dbas,

I'm looking for a solution for the next problem

I'm getting some data from the msdb system database.
a) mdsb.dbo.sysjobservers.last_run_date is an int(4) with yyyymmdd date format

b)mdsb.dbo.sysjobserverslast_run_time is an int (4) with HHMM time format.

I would like to convert both columms in datetime and format. But when i try to convert using

"convert(datetime,dbo.sysjobservers.last_run_date)" it's returns me an overflow error . How I can convert the date int to datetime or smalldatetime?

Thanks in advance
Fernando

B0g
Starting Member

19 Posts

Posted - 2004-11-15 : 05:37:31

You can try something like this:

SELECT CAST(CAST(last_run_date AS CHAR(8)) AS DATETIME) FROM sysjobservers

The "last_run_time" it's a little bit dificult. You have to change like this:

124001(int) -> 12:40:01 (char) ... some substring() will do that

After that it's simple:

SELECT CAST(CAST(last_run_date AS CHAR(8) + last_run_time) AS DATETIME) FROM sysjobservers

fvinagre
Starting Member

29 Posts

Posted - 2004-11-15 : 09:56:49

Thanx a lot it was all i need
Works fine!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources