How to Ignore Record with Invalid Dates

[Learnerprog]

Is there a way to ignore record with invalid dates in SQL queries because I am keep getting this error below
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."

Thank you very much in advance.

 

[vongrunt]

Are you using char/varchar datatype for storing dates?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Learnerprog]

Hello vongrunt,
I am using varchar to store my dates

 

[vongrunt]

IMHO dates stored as char/varchar may cause some serious headaches. Lemme explain.

String data types accept literally anything by default. This also includes out-of-range (<Y1900 or cca. >Y2079) and invalid dates (say, Feb 30th). One bad "varchardate" value may cause query to sometimes work and sometimes fail - depending on whether WHERE clause crawled over bad date or not. Such errors are pain in the a**, especially if you need to pull out yearly report ASAP .

The best practice is to use smalldatetime or datetime instead. That way bad date values won't come into database in a first place.

Of course, if your system periodically imports data from "loose" data sources (Excel, legacy apps) that presents another problem: shall I reject entire import, reject only rows containing bad dates, "blank" bad dates... or do something else?

Btw. check function ISDATE() in Books Online. It may help you to "fix" a problem without redesign.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLSister]

I agree with vongrunt about only store dates in a datetime or smalldatetime field. And you can only effectively do datemath with real datefields. (Ie find the records from the last 30 days)

ISdate will help you identify the items that are not valid dates at all (like 'ASAP' in the DeuDate field) but it won;t help you identify those out fo range for a small datetime field. Likely you habe somerecords where a person mistyped the year, say 1/1/20005. You will need to find these by searching for dates above the range and below the range. Since the data is in char data, you will need to convert it to a date first to this more than likely. If you can do this, then the date data type has enough range for your data and the smalldate does not.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[Learnerprog]

I did use the function isdate but it did still give me the problem. I do import these data from the legeacy systems ie. Dbase III. Is there anyway to get around this?. I am using the DTS by the way. Thank you much.

 

[Learnerprog]

I have found that the function Isdate return the date value
02/10/9620 as true but SQL 2000 won't take it. Any suggestion? Thanks, Joe

 

[vongrunt]

Here is one idea:

set dateformat mdy
declare @blah table (datestr varchar(10))
insert into @blah values ('12/22/2005') -- OK date
insert into @blah values ('SOON') -- self-explanatory :)
insert into @blah values ('12/31/1899') -- out of smalldatetime range
insert into @blah values ('12/31/1752') -- out of datetime range
insert into @blah values ('12/31/2267') -- Beam me up Scottie

select datestr, 
	isdate(datestr),
	[b]case when isdate(datestr)=1 and convert(datetime, datestr) between '01/01/1900' and '06/06/2079 23:59' then 1 else 0 end[/b]
from @blah

As SQLSister already explained, ISDATE() checks for invalid dates and dates outside datetime range (Y1753-Y9999). Since smalldatetime has smaller range (Y1900 - June 6th 2079), rows #3 and #5 pass ISDATE() check but fail during conversion to smalldatetime.

Line in bold works because SQL2k apparently optimizes Boolean expressions - if ISDATE() returns 0, the rest of condition (after AND) would not be evaluated. Most of procedural languages work that way (except VB :E); in SQL it is considered 50% hack but hey, better hack that works than runtime error.

You can put similar code into user-defined function (UDF) - good for code reuse, quite bad for performance when used frequently. Again, the best approach is to not allow cr@p dates to come into database.

Btw. dBase date column accept dates between 01/01/0001 and 12/31/9999. That's only the part of a problem; perhaps application using dBase has poor validation or something?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Learnerprog]

Thanks a lot guys. I got a way to work around it .