CODEWITHSUNDEEP
sqlsql-servert-sql
Madam Zu Zu
Madam Zu Zu

Reputation: 6605

Getting an error when trying to compare two dates

i have a select where i need to make sure that my adate varchar(8) is between two dates supplied.

sometimes the passed in values are NULL so i would then show everything.

select something from myTable where
convert(date, adate) >= ISNULL(convert(date,@beginDate), convert(date, adate))

works fine

but when i make it:

select something from myTable where
convert(date, adate) >= ISNULL(convert(date,@beginDate), convert(date, adate)) 
and convert(date, adate) <= ISNULL(convert(date,@endDate), convert(date, adate))

i get:

Conversion failed when converting date and/or time from character string.

or i can do the <= line without the >= without an error, but not both of them together, what's going on??

the two lines look identical, i can't figure out what i'm doing wrong here.....

Upvotes: 1

Views: 96

Answers (3)

Kaf
Kaf

Reputation: 33809

It looks both @startdate and @enddate are string types. Best thing is to have them in ISO format (yyyymmdd) and you should be fine.

For example try using these;

Select @startdate = '20130101', @enddate = '20130221'

Upvotes: 1

Chains
Chains

Reputation: 13157

Check your value for @endDate. That parameter is your only difference between the SELECT that works, and the one that doesn't. Chances are, it (sometimes) contains a value that cannot be converted to a date. Usual suspect is the empty string ('')

Upvotes: 0

Rafael
Rafael

Reputation: 1525

I'm not sure on what you're trying to do but... Since any of the values could come by NULIFIED, you said you would get every result in the table... so it might be easier just to set the query to filter it on the language you're using before the DB...

Or as I would put on a PHP sample...

    IF ( !$beginDate || !$endDate )
    {
        $query = "SELECT * FROM myTable";
    }
    ELSE
    {
        $query = "SELECT * FROM myTable WHERE 'whatever clause'";
    }

Upvotes: 0

Related Questions