2

I have a table that holds date and time values as INT data type. eg a date is stored as YYYYMMDD, like 20110901 and a time is stored as HHMMSS, like 21826.

The date is close to ISO standard so its not too big an issue however can anyone suggest a simple way to convert to a TIME type please? I have come up with a hideous combination of concatenation, reversing, substring, convert that I am worried will be difficult to read by other staff.

Fatherjack
  • 419
  • 3
  • 12

2 Answers2

4

What you want to do is take your "hideous combination" and adapt it to convert these integer pairs to datetime values. Then test the heck out of it with as many edge cases as you can come up with, perhaps even verify it by hand against a random subset of your actual data. If you're very lucky, you'll be able to use existing code that displays your data as a string and write a script that compares your conversion code output with actual output to make sure every instance lines up and guarantee perfection.

When the code is ready, use it exactly one time to copy the data to a new datetime column that you will add to the table for this purpose. After that is done, remove the old column and rename the new one to match the old name. Now update any code that inserts/updates/reads from the original column to use a datetime data type. Then never speak of this again.

To make all this happen smoothly you'll need to coordinate running your table adjustment scripts with deploying new application code. If this is a 24/7 app that can be tricky, but the result should be worth it.

Joel Coel
  • 12,910
  • 13
  • 61
  • 99
1

This is about the best I can come up with. 

select
-- convert string to time value
CONVERT(time
    -- insert a colon between hours and minutes (position 3)
    , STUFF(
        -- insert a colon between minutes and seconds (position 5)
        STUFF(
            -- convert to string, pad left with zeroes to 6 characters
            RIGHT('000000' + CONVERT(varchar(6), @time_value_int), 6) 
        , 5, 0, ':')
    , 3, 0, ':')
) as time_converted

I initially started down the math route by taking the integer time value and using integer division and modulus to get the hours, minutes and seconds parts, and multiplying by the float equivalent of that time part (hours = 1 / 24.0, minutes = ( 1 / 24.0 / 60.0 ), seconds = ( 1 / 24.0 / 3600.0 ) then adding those together, only to find that you can't convert a float to the TIME data type. It does covert to DATETIME, which you can then convert to TIME, but it seems there is a loss in precision, so the result may be a few milliseconds off.

So the text-munging approach seems cleaner.

kozloski
  • 121
  • 4