November 7, 2013 at 11:43 am
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!!
November 7, 2013 at 11:48 am
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.
November 7, 2013 at 11:56 am
Thanks Luis! I'm sure that'll get me going.
November 7, 2013 at 12:01 pm
You're welcome. Those functions would have helped me a lot in my previous job but we didn't have 2012 🙁
November 7, 2013 at 12:22 pm
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....
November 7, 2013 at 12:46 pm
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
November 7, 2013 at 11:51 pm
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
November 8, 2013 at 11:16 am
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?
November 8, 2013 at 4:54 pm
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
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply