Validate DateTime String in SQL Server 2005

Question

Hi,

I require a SQL script to validate a VARCHAR field in a table in a SQL Server 2005 database that contains DateTime values, in the format DD/MM/YYYY, or NULL values. I would like to identify all invalid dates. Can anyone suggest a method?

UPDATE

• The answer has to make use of T-SQL; for performance reasons, I can't make use of SQLCLR.

Thanks, MagicAndi

Answer 1

You could use the ISDATE() function

Answer 2

you can use the inbuilt T-SQL IsDate() function. and change the column to be datetime not varchar. you can't sort or do any date calcualtions on a varchar column.

Answer 3

SQL's date handling isn't great, we wrote a DotNet function to do our DateTime conversions for difficult case.

Answer 4

Use "ISDATE()" OR "IS NULL": but set the language first to recognise the day-month-year order

SET LANGUAGE british
SELECT ISDATE('12/31/2009'), ISDATE('31/12/2009')

SET LANGUAGE us_english
SELECT ISDATE('12/31/2009'), ISDATE('31/12/2009')

Edit: As mentioned by "Edoode", you can use SET DATEFORMAT too. SET LANGUAGE implicitly sets DATEFORMAT, SET DATEFORMAT overrides SET LANGUAGE

Answer 5

You should specify the dateformat when using ISDATE(). From Books Online:

SET LANGUAGE us_english;
SET DATEFORMAT dmy;
SELECT ISDATE('15/04/2008'); --Returns 1.

Answer 6

I have a solution, although it applies only in a certain set of circumstances. Your case may or may not be right for this.

Create a table called ALMANAC with one row for every valid date. You can populate it with ten years of valid dates with only 3,653 rows, more or less. Even if you go for a hundred years, that's still only 36,525 rows, not too terribly big by today's standards.

You can add strange company specific attributes to this table if it's useful. These are attributes like whether the date is a company specific holiday or not, and what fiscal week, fiscal month, fiscal quarter, and fiscal year the date belongs to.

You'll have to write a program to populate this table, and this program will have to have your company specific calendar rules embedded, if you choose the extra attributes.

Then, you can use this table just like you would use any code validation table. You may also be able to use it to make quirky calendar driven reporting criteria absolutely trivial to implement.

This is only a good idea if your dates are limited to a limited time span, like ten years or maybe even a hundred years. But sometimes, it can really be a time saver! I have sometimes used this technique to make my database independent of one vendor's calendar functions.