How to compare a DATETIME to a VARCHAR date that can have different lengths?

In SQL Server 2008 R2, I have to compare two dates of birth from different tables.

• column C.date_of_birth is of type DATETIME
• column R.dob is a date stored as VARCHAR, but with no leading zeroes on the month or day parts to make its length consistent (<--that's the tough part).

I've gotten as close as this:

where R.dob <&g开发者_高级运维t; convert(varchar, cast(C.date_of_birth as DATE), 101)

but that returns too many rows because 1/5/1923 in R.dob does not match 01/05/1923 in C.date_of_birth.

How can I format R.dob so it can correctly be compared to C.date_of_birth?

Thank you.

---

You can cast your varchar as a datetime. For instance, the result of this:

select cast('1/5/1923' as datetime)

Would be 1923-01-05 00:00:00.000

Then you can just use DATEDIFF() to compare them to see which are equal to the day (or whatever interval you so desire).

---

What about this?

declare @date_of_birth datetime = '1923-01-05',
        @dob varchar(10) = '01/05/1923'

select  @date_of_birth,
        CONVERT(datetime, @dob, 101), -- 101: mm/dd/yyyy
        case    when @date_of_birth = CONVERT(datetime, @dob, 111)
                then 1 else 0
        end result

---

I would try to cast the varchar field to date and do the comparison; for example all of this formats can be easily be converted to date type:

SELECT cast ( '1/1/2011' as date) col1
union
SELECT cast ( '01/11/2011' as date) col1
union
SELECT cast ( 'Oct 7 2011' as date) col1
union
SELECT cast ( '2011-02-23' as date) col1

Outputs:

col1
----------
2011-01-01
2011-01-11
2011-02-23
2011-10-07

So in your query you can compare dates to dates.

---

As others have said, first option would be to change the data type of R.Dob to Date.

Otherwise you should cast your varchar to a date and by your comments you get an error doing that. The reason for that is probably because you have invalid dates in the column. You could make use of the isdate function to figure out what rows have invalid dates.

Another reason could be that your SQL Server does not know how to interpret your date strings. I don't know how to interpret this value 1/5/1923 so how could SQL Server know?

Use set dateformat to tell SQL Server how to interpret the date string.

set dateformat mdy
select cast('1/5/1923' as date)

set dateformat dmy
select cast('1/5/1923' as date)

Result:

----------
1923-01-05

----------
1923-05-01