troubleshooting Question

How do I test a CAST conversion will work when it is used in a WHERE statement?

Avatar of philwill4u
philwill4u asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
22 Comments2 Solutions244 ViewsLast Modified:
How do I test a CAST conversion will work when it is used in a WHERE statement?

SELECT a.month, a.year from gift_recipient
WHERE
      datediff(dd,convert(datetime,cast(year(getdate()) as varchar)+'/'+cast(month(getdate()) as varchar)+'/'+cast(day(getdate()) as varchar),111) ,convert(datetime,cast(a.year as varchar)+'/'+cast(a.month as varchar)+'/'+cast(a.date as varchar),111) ) = 14

Unfortunately, when the returned a.month = 0 (as it can be), the CAST fails with
'Conversion failed when converting datetime from character string.'

When a 0 is encountered (default mont/ year), I'd like to set the condition 0=14  so that it fails gracefully.

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 22 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros