How to convert int to real-time dimensions

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

Transact-SQL (2005)

How to convert int to real-time dimensions

Author

Topic

mivey4
Yak Posting Veteran

66 Posts

Posted - 2007-10-27 : 22:26:37

Hi

I am trying to derive some way of converting a time column for one of the jobs tables to a real-time output. Does anyone have a T-sql function or script that will convert a time value stored as
int to a real time like 10:30 or something similar?

If not, does anyone know how to read an int time stored in SQL? For example, sp_help_jobhistory returns date and time values as ints and I need to convert this into a human legible format.

Any help would be much appreciated.

Thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-10-27 : 23:01:30

see dateadd and datediff functions in BOL

mivey4
Yak Posting Veteran

66 Posts

Posted - 2007-10-28 : 08:07:20

Thanks for the reply Russell but I don't believe you fully understand what I am asking. Datediff will provide a method of determining the difference between 2 given dates and Dateadd will a increment a portion of a datetime value.

I have used these functions and believe I know that they won't perform the task that I am asking.

Let me explain the problem once more. If you're running SQL 2000 and have any jobs that have been executed, you could perform the a query as the following:

select last_run_time from msdb.dbo.sysjobsteps

and receive returned values that contain the last time a job was executed "stored in integer datatype" columns. See ->
sp_help sysjobsteps.

In SQL 2005 I believe the concept is the same. I believe the intent of Microsoft for this was to store the date separate from the time values which won't work using the datetime datatype and I have read this in documentation in the past.

The challenge is to convert that data into a human legible 12 or 24 hour time format like 11:00 AM or 02:45:39. I am not aware of either functions you've suggested being capable of performing this task.

Does anyone have any further suggestions to assist in resolving this problem???

Thanks.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-28 : 10:17:13

Something like this? I use this function occassionaly, or formats of it to convert the agent start and end times in the schedule table

Create function fInttoTime (@time int)
returns datetime
as
begin
declare @date datetime,
@hour int,
@min int,
@sec int,
@datestr nvarchar(40)

select @hour = (@agenttime / 10000)
select @min = (@agenttime - (@hour * 10000)) / 100
select @sec = (@agenttime - (@hour * 10000) - (@min * 100))

select @datestr = replace(convert(nvarchar(2), @hour) + N':' +
convert(nvarchar(2), @min) + N':' +
convert(nvarchar(2), @sec), ' ', '0')

select @date = convert(datetime, @datestr)

return @date
end

this one does the whole date. The code is online somewhere, can't remember where I found it last year.

create function fAgentDateFormat] (@agentdate int, @agenttime int)
returns datetime
as
begin
declare @date datetime,
@year int,
@month int,
@day int,
@hour int,
@min int,
@sec int,
@datestr nvarchar(40)

select @year = (@agentdate / 10000)
select @month = (@agentdate - (@year * 10000)) / 100
select @day = (@agentdate - (@year * 10000) - (@month * 100))

select @hour = (@agenttime / 10000)
select @min = (@agenttime - (@hour * 10000)) / 100
select @sec = (@agenttime - (@hour * 10000) - (@min * 100))

select @datestr = convert(nvarchar(4), @year) + N'-' +
convert(nvarchar(2), @month) + N'-' +
convert(nvarchar(4), @day) + N' ' +
replace(convert(nchar(2), @hour) + N':' +
convert(nchar(2), @min) + N':' +
convert(nchar(2), @sec), ' ', '0')

select @date = convert(datetime, @datestr)

return @date
end

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-28 : 10:29:28

Also you can simple add "0" to the time stamp and use convert...

Cast (agenttime + 0 as datetime)

or use Convert to format it specifically per BOL.

refer to this http://weblogs.sqlteam.com/jeffs/archive/2007/08/29/SQL-Dates-and-Times.aspx

a simple search of the site on SQL Date and times reveals numerous suggestions.

mivey4
Yak Posting Veteran

66 Posts

Posted - 2007-10-28 : 10:33:39

Thanks dataguru1971!!!

The link is much appreciated as well.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-28 : 13:29:48

This forum is for working scripts to share with the community, not for asking questions. So I am moving this thread to another forum here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources