Can I get a list of rows with varchar values that aren't castable to datetime?

Question

I have a legacy SQL Server 2000 database that has one column that has a date that is stored as a varchar instead of a datetime for whatever reason. I want to make a view against this data and use a datetime field instead of a varchar to make making a report against the field easier. The good news is that most of the values look like they should be able to be cast directly into a datetime.

Of course, there apparently are some fields that were entered in such a way that they aren't castable to datetime. Just for clarity's sake, here's how the table could look:

CREATE TABLE dbo.user
(
    ...
    birthdate varchar(10)
    ...
)

And the view could look something like this:

CREATE VIEW dbo.UserView
AS
SELECT ...
       CAST(u.birthdate AS datetime) AS birthdate
       ...
FROM user u
WHERE ...

Is there any way I can:

1. Get a list of all the rows where birthdate cannot be cast into a datetime in case I can repair it?
2. In instances where I can't repair the value, make the value show up as NULL or maybe even something that is obviously not the user's real birthdate?

Answer 1

Use IsDate()

SELECT birthdate, ' ' _, *
FROM user u
WHERE IsDate(u.bithdate) != 1

and

SELECT ...
   CAST(CASE WHEN IsDate(u.birthdate) = 1 THEN u.birthdate ELSE NULL END AS datetime) AS birthdate
   ...
FROM user u
WHERE ...

Answer 2

Check the T-SQL ISDATE() Function

Answer 3

show bad rows:

select * from dbo.user where isdate(birthdate)!=1

your view:

SELECT ...
    CASE
        WHEN isdate(birthdate)=1 THEN CAST(u.birthdate AS datetime)
        ELSE NULL
    END AS Birthday
       ...
FROM user u
WHERE ...

Answer 4

Thanks........ good solution solved my problem