Results 1 to 7 of 7

Thread: How to convert Time from int to Real-time

  1. #1
    Join Date
    Oct 2007
    Posts
    15

    How to convert Time from int to Real-time

    Hi all.

    I have posted this question in another forum but no one has so far been able to provide a solution to the problem. Since I know Database Journal always has very informative and enlightening posts, I figured I'd post the question here in hopes that some guru can provide an answer.

    If you're running SQL 2000 and have any jobs that have been executed, you could perform a query as such:

    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 think the intent of Microsoft had it mind for doing 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 humanly legible 12 or 24 hour time format like 11:00 AM or 02:45:39.

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

    Thanks.

  2. #2
    Join Date
    Oct 2007
    Posts
    3

    Smile Suggested syntax

    Hi Mindscape,

    I use the datename function in some syntax like this for presenting datetime data types in human readible format. It works in SQL 2000 and 2005:

    declare @today as datetime
    set @today = getdate()
    select datename(month,@today)+space(1)+
    datename(day,@today)+','+space(1)+
    datename(year,@today)

  3. #3
    Join Date
    Oct 2007
    Posts
    3
    DateTime when expressed as a float uses the fractional part to describe the time and the whole number part to describe the date.
    Looking at my sql 2005 box, the values for last_run_time in msdb.dbo.sysjobsteps appear to be this fractional time part but stored as an int. If I am correct that this is what the last_run_time is stored as, then you can convert to a DateTime of the correct time on 1900-01-01 by doing:

    select
    case last_run_time
    when 0 then cast(0 as datetime)
    else cast(cast(last_run_time as float) / power(10, floor(log10(last_run_time))+1) as datetime)
    end as last_run_time_dt
    from msdb.dbo.sysjobsteps

    Notes:
    -have to case out the 0 values or else it errors
    -there may be some simpler math approach to reliably convert both 72 to .72 and 840 to .84, but I'm not sure what it is. So this method determines what power of 10 we need to divide last_run_time to turn it into a fraction between < 1 and >= .1

    Hope that helps!
    -Adrian Hains

  4. #4
    Join Date
    Dec 2004
    Posts
    502
    I have an ugly, but hopefully correct way of converting those times:

    REVERSE(STUFF(STUFF(REVERSE(REPLICATE('0', 6 - LEN(CONVERT(varchar, last_run_time))) + CONVERT(varchar, last_run_time)), 3, 0, ':'), 6, 0, ':'))

    And if you need to combine the time with the last_run_date, it gets even uglier:

    REPLACE(CONVERT(varchar, CONVERT(datetime, CONVERT(char(10), CONVERT(datetime, CONVERT(varchar, last_run_date)), 101) + ' ' + REVERSE(STUFF(STUFF(REVERSE(REPLICATE('0', 6 - LEN(CONVERT(varchar, last_run_time))) + CONVERT(varchar, last_run_time)), 3, 0, ':'), 6, 0, ':'))), 109), ':000', '')

  5. #5
    Join Date
    Oct 2007
    Posts
    3
    Quote Originally Posted by nosepicker
    I have an ugly, but hopefully correct way of converting those times:
    Thank you for that, it makes my method look not hacky at all in comparison

  6. #6
    Join Date
    Oct 2007
    Posts
    3

    Maximum obfuscation

    Wow, I've written some pretty ugly T/SQL but nosepicker's surpasses all. -- although, it does appear to work. I wouldn't put anything like it into production.

  7. #7
    Join Date
    Oct 2007
    Posts
    3

    Suggested syntax - revision 1

    This example takes a julian date, expressed as a decimal, and converts it to human readable hour with meridian:

    select substring(cast(convert(datetime, 39750.33333335, 101) as varchar),13,20)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •