Testing for legitimate Time data type

Question

Post reply

Testing for legitimate Time data type

RedBirdOBX

SSCommitted

Points: 1836
More actions
November 7, 2013 at 11:43 am

#284855

I've working on a project where I'm importing all kinds of filthy, dirty data into a new normalized database. The challenge for today is dealing with Time types.
I have a table which contains all kinds of "times". Unfortunately, these are just wide open Char fields and the user could type whatever they want. The majority of it is clean but a huge percentage is not.
The column might contain:
8:00
8:00 am
8:00 AM
8:00 A.M
08:00
Yoda
5:00 p.m.
17:00
and so on.
What I'd like to do is use a CASE WHEN just import anything that passes as legit and then simply write 0:00 or even make it Null if it does not pass.
I looked around before posting and didn't have much luck. Does anyone know of a function that can test for valid times? Is there anything like a " IsTime " function?
SQL 2012
Thanks!!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Have you tried TRY_CAST() or TRY_CONVERT()?

You're posting on a SQL Server 2012 forum, so I expect that you're working on it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 2

RedBirdOBX

SSCommitted

Points: 1836

November 7, 2013 at 11:56 am

#1664885

Thanks Luis! I'm sure that'll get me going.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

You're welcome. Those functions would have helped me a lot in my previous job but we didn't have 2012 🙁

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 4

I just realized that the source database is SQL 2008 although the destination is 2012. Oh boy....looked online some more and it seems TRY_CAST or TRY_CONVERT is not an option. Bummer....

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

You could do something like this:

WITH Sampledata AS(

SELECT *

FROM (VALUES

('8:00'),

('8:00 am'),

('8:00 AM'),

('8:00 A.M'),

('08:00'),

('Yoda'),

('5:00 p.m.'),

('17:00'))x(charTime)

)

SELECT charTime,

CASE WHEN charTime LIKE '[0-2][0-9]:[0-5][0-9]%' OR charTime LIKE '[0-9]:[0-5][0-9]%'

THEN CAST(REPLACE( charTime, '.', '') AS time)

ELSE NULL END

FROM Sampledata

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

You can test them with the ISDATE function.

select

a.MyTime,

IsTime = isdate(a.MyTime)

from

(

select MyTime ='8:00'

union all select MyTime = '8:00 am'

union all select MyTime = '8:00 AM'

union all select MyTime = '8:00 A.M'

union all select MyTime = '08:00'

union all select MyTime = 'Yoda'

union all select MyTime = '5:00 p.m.'

union all select MyTime = '17:00'

) a

Results:

MyTime IsTime

--------- -----------

8:00 1

8:00 am 1

8:00 AM 1

8:00 A.M 0

08:00 1

Yoda 0

5:00 p.m. 0

17:00 1

RedBirdOBX SSCommitted Points: 1836 More actions · Answer 7

This one is giving me a lot of grief!!

Check this out:

SELECT

CASE

WHEN ISDATE([InspectionTime]) = 1 THEN

CONVERT(TIME, [InspectionTime], 114)

ELSE

[InspectionTime]

END

FROM Inspection38

"InspectionTime" is a field that will contain some bad data (invalid times). This simple query is failing b/c it's still trying to convert the 'date' to a string.

if I remove the Else clause, it just outputs a Null as soon as it can't convert the data into time. But if I put the Else clause back in, it fails.

Conversion failed when converting date and/or time from character string.

Feeling like a newbie here. Any thoughts?

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 8

RedBirdOBX (11/8/2013)
This one is giving me a lot of grief!!
Check this out:
SELECT
CASE
WHEN ISDATE([InspectionTime]) = 1 THEN
CONVERT(TIME, [InspectionTime], 114)
ELSE
[InspectionTime]
END
FROM Inspection38
"InspectionTime" is a field that will contain some bad data (invalid times). This simple query is failing b/c it's still trying to convert the 'date' to a string.
if I remove the Else clause, it just outputs a Null as soon as it can't convert the data into time. But if I put the Else clause back in, it fails.
Conversion failed when converting date and/or time from character string.
Feeling like a newbie here. Any thoughts?

Slow down and think it out... what kind of column does the CONVERT TIME create as compared to your ELSE? You cannot display the original bad item in the same column as the CONVERT because the CONVERT is converting the good data to a TIME datatype and the result of the ELSE isn't going to work on the TIME datatype.

You're also limiting yourself on some perfectly valid times that might just be missing a period, etc. Try something like this.... (since I don't have 2008 on the machine I'm currently working on, I CAST to a formatted time instead of a TIME datatype. You can change it to meet your needs).

WITH Sampledata (charTime) AS

( --=== This is just test data and is not a part of the solution

SELECT '8:00' UNION ALL

SELECT ' 8 : 00 a m' UNION ALL

SELECT '8:00 AM' UNION ALL

SELECT '8:00 A.M' UNION ALL

SELECT '08:00' UNION ALL

SELECT 'Yoda' UNION ALL

SELECT '5:00 p.m.' UNION ALL

SELECT '5:00 a.m.' UNION ALL

SELECT '17:00' UNION ALL

SELECT '25:00' UNION ALL

SELECT '25:00 a.m.' UNION ALL

SELECT '1' UNION ALL

SELECT '25 00' UNION ALL

SELECT '2300'

)

SELECT charTime

,Converted = CASE

WHEN ISDATE(ca.RepairedTime) = 1 AND ca.RepairedTime LIKE '%:%'

THEN CONVERT(CHAR(8),CAST(ca.RepairedTime AS DATETIME),108)

ELSE NULL

END

FROM Sampledata

CROSS APPLY(SELECT REPLACE(REPLACE(charTime,'.',''),' ',''))ca(RepairedTime)

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)