December 8, 2009 at 5:38 am
Hello board
Often, when I try to import old date from a table, where the date is stored as varchar to a table, where the same field should be smalldatetime, I get conversion or out-of-range errors. Is there any easy way of identifying which row or value caused the error? I only get error messages such as
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
That may help me finding out which column caused the error, but if the table consists of thousands or millions of rows, the error message does not help me at all finding out which value was the cause. One way of identifying it is to use TOP ... and stepwise extend the range until you got your error message. But there must be a simpler way, a setting, a special query ...
Thank you very much for any hints!
Hanspeter
December 8, 2009 at 5:56 am
I've already found kind of a solution for the out-of-range error, that will produce NULL instead of an error (you just have to go through all NULLS instead of the entire table), with the help of the following article:
http://www.sommarskog.se/error-handling-I.html#anatomy
-- out-of-range
---------------
-- Default:
SET XACT_ABORT OFF
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SELECT CONVERT(datetime, '20031234') -- This causes an overflow
GO
-- Adjusted settings:
SET XACT_ABORT ON-- very most of the statement-terminating errors instead become batch-aborting errors
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SELECT CONVERT(datetime, '20031234') -- NULL instead of error-message
Unfortunately, this doesn't work for conversion errors 🙁
December 8, 2009 at 6:01 am
How is your date stored in char data type, I mean the format? what is the conversion that you are doing?
---------------------------------------------------------------------------------
December 8, 2009 at 6:13 am
e.g. the following simple examples:
SELECT CONVERT(datetime, '20031234') -- This causes an overflow
SELECT CONVERT(datetime, '2003123') -- This causes a conversion error
In more practical terms, I was trying to import a table containing a field of type varchar(8). This field should be converted to smalldatetime. The old table not only contained valid dates, but also
'00000000'
' 0'
--> conversion errors
and
'Feb 29 2'
--> out-of-range error
I found the invalid values manually, and am looking for a way to make SQL-Server tell me which values are invalid.
Thank you very much and best regards
Hanspeter
December 8, 2009 at 6:17 am
Checkout ISDate,
http://msdn.microsoft.com/en-us/library/ms187347(SQL.90).aspx
---------------------------------------------------------------------------------
December 8, 2009 at 6:49 am
Note that isdate() is not fully reliable
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx
Failing to plan is Planning to fail
December 8, 2009 at 7:00 am
Thank you very much for the hints, that works nicely, so far!
However, is there also a solution for other conversions than date-conversions? (e.g. overflow when converting to smallint, conversion error when converting varchar to int (when it contains other chars than numbers) etc.)?
Basically, is there any way to make SQL-Server tell you which value caused the error?
Thank you
Hanspeter
December 8, 2009 at 7:07 am
A quick way should be
SELECT date_col from your_table
where right(date_col,2)>31 or substring(date_col,5,2)>13
Failing to plan is Planning to fail
December 8, 2009 at 8:43 am
That's a good idea, but it would still not recognize cases such as
'20020229'
'0'
'00000000'
Thank you anyway 🙂
December 8, 2009 at 11:07 am
pesche_h (12/8/2009)
Thank you very much for the hints, that works nicely, so far!However, is there also a solution for other conversions than date-conversions? (e.g. overflow when converting to smallint, conversion error when converting varchar to int (when it contains other chars than numbers) etc.)?
ISNUMBER is one thing you may have to look at and to check if the number would fit into smallint you can do a case statement and check the range and return whatever you want. (Not sure if there is any other elegant way
Basically, is there any way to make SQL-Server tell you which value caused the error?
Thank you
I think its all about handling it graciously and i dont think there is any straightforward way.A combination of several things could get what you want to know ( I could be wrong) and see Try catch block as well
---------------------------------------------------------------------------------
December 8, 2009 at 9:40 pm
Dear Tally Table. How I love thee, let me count the ways...
SELECT *
FROM YourTable
WHERE pseudodatecolumn NOT IN (
select CAST(YEAR(dateadd(d,n,'19910101 00:00:00')) as char(4)) +
RIGHT('0' + CAST(MONTH(dateadd(d,n,'19910101 00:00:00')) as varchar(2)),2) +
RIGHT('0' + CAST(DAY(dateadd(d,n,'19910101 00:00:00')) as varchar(2)),2)
from tally
WHERE N < 10000 --(This would go from 1991 - 2018, adjust your date range accordingly)
)
See the article in my signature for what a tally table is. I'll post a cte version tomorrow at work (or someone else can).
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply