SqlDbType.Date SQL Server 2005

Question

I have a property like this:

private SqlParameter DateOfBirthSqlParameter
{
    get
    {
        return new SqlParameter("@date", SqlDbType.Date)
            {
                Value = dateOfBirth.ToString("MM/dd/yyyy");
            };
    }
}

dateOfBirth is a DataTime C# type. I use this parameter to add to my SQL command, that look like this.

Select * From mytable Where dob_dt = @date

The problem is that doesn't work in SQL Server 2005. I got an exception:

System.ArgumentException: The version of SQL Server in use does not support datatype 'date'

This because SQL Server 2005 doesn't support Date, this is a feature of later version (SQL Server 2008).

There are many solutions to this, for instance change SqlDbType.Date to SqlDbType.DateTime

I guess the reference System.Data is a version that provide all features supported for SQL Server 2008. Should I change it to something that only support 2005? that will help to discover other possible mismatches? What is the best way to handle this situation?

Thanks.

Update: 8/31/2012

In the example, I used date of birth but In my application I used something else such as purchase_dt or something like it (with the time on it). My first Select Statement was:

Select * From mytable Where Cast(purchase_dt as Date) = @date

and that was fine in my computer with SQL Server 2008 and in another PC with SQL Server 2005 also was fine and just throws an exception when it got to the select.

But, once I fixed that, with:

Select * From mytable Where Cast(DATEDIFF(dd, 0, purchase_dt) as DateTime) = @date

This broke the entire web application (in the other PC)... The problem was SqlDbType.Date. but Why it didn't break it before?

Answer 1

private SqlParameter DateOfBirthSqlParameter
{
    get
    {
        return new SqlParameter("@date", SqlDbType.DateTime)
            {
                Value = dateOfBirth.ToString("MM/dd/yyyy");
            };
    }
}

Answer 2

Data type DATE is not supported on SQL Server 2005, DATE type is supported form SQL Server 2008 version. Instead you could use datetime or smalldate