Getting error in this query

It sounds like TInsuranceExp is a string data type?  If so then I supect the content in it does not conform to one of the supported text date time formats supported by SQL Server.  What is it's data type, and what is the value it contains?

• CAST and CONVERT (Transact-SQL) | Microsoft Docs

---

»bp

Select TInsuranceExp from TableA where isdate(TicklerInsurance)=1 and TInsuranceExp  > Getdate()

I think you wanted to test the same field you wanted to use as implicit date, so:

Select TInsuranceExp from TableA 
where isdate(TInsuranceExp)=1 and TInsuranceExp  > Getdate()

Select allOpen in new window

or perhaps

Select TInsuranceExp from TableA 
where isdate(TicklerInsurance)=1 and TicklerInsurance  > Getdate()

Select allOpen in new window

Bye, Olaf.

I sometimes see this when there a rouge date value. Try sorting the table by TInsuranceExp and look at the smallest and largest values. SQL has a relatively narrow range of dates. If you see 200 when you expect 2000 for a year, that will cause this error.

kelvin

SQL doesn't necessarily run things in the same order you have them in the WHERE clause.  SQL could be checking the "> getdate()" part even though the column would not pass the isdate() check.

However, you can force SQL to do the checks n order by including them in a CASE statement, which is always executed sequentially:

Select TInsuranceExp, ...
from TableA
where 1 = case when isdate(TicklerInsurance)=0 then 0
                            when TInsuranceExp > Getdate() then 1
                            else 0 end

if I looked at the column, it is populated by correct date format, I see blanks and 0 in that field. The data type is varchar

'0' is not a valid date.  Yes, 0 is, and '' is, but '0' isn't.  Go figure that one out ... but drink a few beers while you're doing it!

Which column are you describing?

---

»bp

TInsuranceExp

Then you also want to check isdate(TInsuranceExp)=1 and not check isdate(TicklerInsurance)=1

Another way to enforce order of checks would be:

Select innerquery.TInsuranceExp, innerquery.TicklerInsurance from
(Select * from TableA where isdate(TInsuranceExp)=1) innerquery
Where innerquery.TInsuranceExp  > Getdate()

Select allOpen in new window

Or with CTE.

Taking Scotts solution should also work, but then again using the correct field...

Select TInsuranceExp, ...
from TableA 
where 1 = case when isdate(TInsuranceExp)=0 then 0
                            when TInsuranceExp > Getdate() then 1
                            else 0 end

Select allOpen in new window

Bye, Olaf.

Big question is, what do you want to do with the rogue entries - do you need to fix them, or, just get the valid dates ? Should we compare "dates" or should we be comparing "strings" ? sometimes it is easier to match the other way around.

We know the rogue entries are zero, but, what format are the valid dates in ?

Might be easier to do a string comparison especially if YYYYMMDD format

Select TInsuranceExp from TableA where TInsuranceExp  > convert(char(8),Getdate(),112)

It only has to do convert(char(8),Getdate(),112) once

We can refine if you can give more information, like what is "the correct date format" you mentioned above ? We have more tools to manipulate getdate() like datename(), substring(), left(), right() etc easier to make getdate() fit the varchar field than the other way around.

the 112 is a style code. in https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql , and scroll down to style codes

I agree with Mark in that ISDATE(string)=1 is quite a monstrous construct, in that it will yield 1 for very different strings, not all matching a certain pattern you'd define for your string dates when you would use an explicit CONVERT() as Mark suggests. So an implicit date/datetime conversion you want to use, when you write field>GetDate() may implicitly convert many strings into datetimes you wouldn't think of as being convertable.

It's not a good idea to rely on implicit conversions. Which means error-free execution of implicit conversions doesn't mean conversions you'd consider valid. Corner cases of implicit conversions can be seen from using the simpler CAST(string as datetime), for example CAST('04/2008/15' as datetime) works, gives 15th April 2008 and also is compared as that date with getdate(), when you write '04/2008/15'<GETATE(), no matter if literally '04/2008/15' or '04/2008/15' is the string value of a varchar field.

Bye, Olaf.

Just passing by:
If the column in question was actually a date life would be a lot simpler.
In recent versions try_cast/try_convert are more fault tolerant alternatives to cast/convert.

0 may be a (special) date, but not according to isdate()

select 'string zero' , isdate('0')  union all
select 'integer zero', isdate(0)    union all
select 'empty string', isdate('')   union all
select 'null'        , isdate(NULL) 
;


+----+------------------+------------------+
|  1 | string zero      |                0 |
|  2 | integer zero     |                0 |
|  3 | empty string     |                0 |
|  4 | null             |                0 |
+----+------------------+------------------+

Select allOpen in new window

http://rextester.com/TNRJ4568