Converting a string to Time format

Question

Converting a string to Time format

Hakuna Matata

SSCertifiable

Points: 5577
More actions
June 29, 2011 at 1:43 am

#238656

Hi,
On my database we are storing the time in a column which has a datatype set to varchar(max).
Example dates in the table: 0615, 515, 2345, 23:45 AM.
Now i need to convert this field to time format . How can I do this?
Regards,
Nithin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

There's no easy built-in way. You could:

(1) Re-import the data from its source in a consistent format recognizable as a time by SQL Server, and store it in a column typed as TIME instead of VARCHAR(MAX).

(2) Run several manual UPDATE queries to convert the mix of current formats into a consistent one and then use ALTER TABLE ALTER COLUMN to change the type to TIME. This will be tedious work and error-prone, so be careful.

There is a lesson here, of course. 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

On my database we are storing the time in a column which has a datatype set to varchar(max).

Not a good idea, as you may have guessed.

I expect lots of pain here.

One possible way is handling separately all the different formats, something similar to this:

;WITH mixedTimesTable (sourceCol) AS (

SELECT *

FROM (

VALUES ('0615'),

('515'),

('2345'),

('11:45 PM')) AS SRC (col)

)

SELECT timeCol =

CASE

WHEN sourceCol LIKE '[0-9][0-9][0-9][0-9]' THEN

CONVERT(time, LEFT(sourceCol,2) + ':' + RIGHT(sourceCol,2) + ':00', 108)

WHEN sourceCol LIKE '[0-9][0-9][0-9]' THEN

CONVERT(time, '0' + LEFT(sourceCol,1) + ':' + RIGHT(sourceCol,2) + ':00', 108)

WHEN sourceCol LIKE '[0-9][0-9]:[0-9][0-9] AM' OR sourceCol LIKE '[0-9][0-9]:[0-9][0-9] PM' THEN

CONVERT(time, sourceCol, 0)

END

FROM mixedTimesTable

If you wrap the CASE statement in a scalar function, you can also handle the unexpected formats with a TRY...CATCH block and return NULL in case of an exception. This would allow refining the code on the go.

Good luck!

Hope this helps

Gianluca

-- Gianluca Sartori

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 3

Thanks a lot!!!!!!! for the timely help. This is of great use to me !!!!!!! Thanks once again !!!!:-):-):-):-)

Paul White SSC Guru Points: 150468 More actions · Answer 4

CELKO (6/30/2011)
First, shoot the idiot that used VARCHAR(MAX) for the string.

Or, better still, educate them. VARCHAR(MAX) is not worth going to jail for. 😉

Seriously, yes VARCHAR(MX) is generally a poor choice of type unless you really are going to store string values greater than 8000 bytes in that column. There's not so much impact on storage because MAX types are stored exactly as if the column were typed VARCHAR(8000), but the Query Processor does treat them differently - after all the MAX column might contain 2GB data.

There are some other subtle effects too, including the fact that a table with a MAX column cannot be rebuilt ONLINE. Overall: use appropriate types, and for sure cleaning data before it gets the the database is much to be preferred over the alternative.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 5

Thanks a lot for the valuable suggestions. I am in the process to fine tuning the database by eliminating all the junk data & standardization of the data types across the database. :-):-):-):-):-)

By the way 23:45 AM was a typo error on my side. It should have been 3:45 AM.