CODEWITHSUNDEEP
sql-server-2008
user477526
user477526

Reputation: 165

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.

I've gotten as close as this:

where R.dob <> 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.

Upvotes: 1

Views: 15311

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 0

Icarus
Icarus

Reputation: 63966

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.

Upvotes: 0

Rubens Farias
Rubens Farias

Reputation: 57946

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

Upvotes: 0

user596075
user596075

Reputation:

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).

Upvotes: 1

Related Questions