• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

Getting error in this query

I got the message "Conversion failed when converting date and/or time from character string."

Select TInsuranceExp from TableA where isdate(TicklerInsurance)=1 and TInsuranceExp  > Getdate()
0
zachvaldez
Asked:
zachvaldez
  • 3
  • 3
  • 2
  • +4
2 Solutions
 
Bill PrewCommented:
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?



»bp
1
 
Olaf DoschkeSoftware DeveloperCommented:
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()

Open in new window


or perhaps

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

Open in new window


Bye, Olaf.
0
 
Kelvin SparksCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
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
0
 
zachvaldezAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
'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!
0
 
Bill PrewCommented:
Which column are you describing?


»bp
0
 
zachvaldezAuthor Commented:
TInsuranceExp
0
 
Olaf DoschkeSoftware DeveloperCommented:
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()

Open 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

Open in new window


Bye, Olaf.
0
 
Mark WillsTopic AdvisorCommented:
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
1
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
 
PortletPaulfreelancerCommented:
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 |
+----+------------------+------------------+

Open in new window

http://rextester.com/TNRJ4568
1
 
Mark WillsTopic AdvisorCommented:
When you let us know the string format of your varchar field, also, what version of SQL ?

 The case statements will work, but would need to use the ELSE

where TInsuranceExp > case when isdate(TInsuranceExp)=0 then '99991231'  ELSE convert(varchar(8),Getdate(),112) end

The trouble is when comparing ordinal with cardinal datatypes.

Now, as mentioned before, we do have a lot of tools for manipulating a date into character string, easiest (starting from sql2012) is FORMAT
SELECT FORMAT ( getdate(), 'd', 'en-US' ) AS 'US English mdy'  
      ,FORMAT ( getdate(), 'd', 'en-gb' ) AS 'Great Britain English dmy'  
      ,FORMAT ( getdate(), 'D', 'en-US' ) AS 'US English Result'  
      ,FORMAT ( getdate(), 'D', 'en-gb' ) AS 'Great Britain English Result'  
      ,FORMAT ( getdate(), 'dd MMM yyy' )  AS 'custom format'  
      ,FORMAT ( getdate(), 'yyyMMdd' )  AS 'best format for str compare'

/* RESULTS
12/19/2017	19/12/2017	Tuesday, December 19, 2017	19 December 2017	19 Dec 2017	20171219
*/  

Open in new window

Even though all the above results are based on GETDATE(), as strings they are impossible to compare. Not such a problem if checking for equality so long as the format is the same.

Then we need to think of ordinal positions of a date part.  For example while it might be visually obvious that '02/10/2019' > getdate() it is quite different when they are strings. As strings, '02/10/2019' is less than '19/12/2017' or '12/19/2017' But if both formats are YYYYMMDD (even with delimiters) then you can compare two strings.

So, while it is relatively easy to get GETDATE() as a string, we might still need to manipulate TInsuranceExp before we can compare.

It could be the case that you need to break apart TInsuranceExp into component parts first.
declare @s1 varchar(20) = '0'
 declare @s2 varchar(20) = '02/10/2019'    -- dmy for Aussie 
 
 select substring(@s1,7,4)+substring(@s1,4,2)+left(@s1,2)
 select substring(@s2,7,4)+substring(@s2,4,2)+left(@s2,2)
 --or
 select right(@s2,4), datename(year,getdate())

Open in new window

Would try to avoid that last one, because have to start accounting for >= and gets unwieldy if getting down to time aspects.

Does that make sense ?
0
 
Scott PletcherSenior DBACommented:
Sorry, I did the date check on the wrong column name:

where ... and 1 = case when isdate(TinsuranceExp)=0 then 0
                                   when TInsuranceExp > Getdate() then 1
                                   else 0 end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now