SQL Server ISDATE() Error

Question

I have a table and need to verify that a certain column contains only dates. I'm trying to count the number of records that are not follow a date format. If I check a field that I did not define as type "date" then the query works. However, when I check a field that I defined as a date it does not.

Query:

SELECT 
    count(case when ISDATE(Date_Field) = 0 then 1 end) as 'Date_Error'
FROM [table]

Column definition:

Date_Field(date, null)

Sample data: '2010-06-27'

Error Message:

Argument data type date is invalid for argument 1 of isdate function.

Any insight as to why this query is not working for fields I defined as dates?

Thanks!

Answer 1

If you defined the column with the Date type, it IS a Date. Period. This check is completely unnecessary.

What you may want to do is look for NULL values in the column:

SELECT SUM(case when Date_Field IS NULL THEN 1 ELSE 0 end) as 'Date_Error' FROM [table]

I also sense an additional misunderstanding about how Date fields, including DateTime and DateTime2, work in Sql Server. The values in these fields are not stored as a string in any format at all. They are stored in a binary/numeric format, and only shown as a string as a convenience in your query tool. And that's a good thing. If you want the date in a particular format, use the CONVERT() function in your query, or even better, let your client application handle the formatting.