Finding invalid data
-
I have a table which contains a date field which is a string with format 'yyyymmdd' I have written a function to convert this to a normal SQL date. However it turns out that one or more records have something wrong with the string which gives an invalid date when trying to convert to sql date. I am a bit stumped as to how to find these odd case as there are 7000000 rows in the table. I've tried looking at @@error after the convert but the batch just gets abandoned as soon as the error occurs.
Any help appreciated.
Alan
-
This might narrow down the rows with invalid date
DECLARE @tbl table (dt varchar(8))
INSERT INTO @tbl
SELECT '20070810'
UNION
SELECT '20070230'
select * FROM @tbl where ISDATE(dt) = 0
-
I have a table which contains a date field which is a string with format 'yyyymmdd' ... It's almost always a bad idea to store formatted dates in tables and bad dates are kinda proof enough of that. Any reason why you can't store it as a DateTime datatype?
...I have written a function to convert this to a normal SQL date. You don't need that function. The 'yyyymmdd' format is the ISO format and is recognized by SQL Server in the intrinsic CAST function. For example, this will work...
SELECT CAST(yourchardatecolumn AS DATETIME) AS TheDate
FROM yourtable
And, if you need make sure you don't get bad dates, something like the following will work...
SELECT CASE
WHEN ISDATE(yourchardatecolumn) = 1 THEN CAST(yourchardatecolumn AS DATETIME)
ELSE NULL
END AS TheDate
FROM yourtable
--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)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply