I can not only replicate this I can explain why it is happening. It is a bit strange but is further proof that implicit conversions should be avoided. When selecting...

Andrew-495157 (9/7/2012)

---

@SSCertifiable

There is a consistency within SQL issue here which I don't understand

Why did the conversion NEVER fail with the value 'AB' is in the column?

This value has been there...

Luis Cazares (9/7/2012)

---

You might find this useful.

SELECTCAST( GETDATE() AS DATE),

DATEADD( DD,DATEDIFF(DD, 0, GETDATE()),0)

If you just need dates with no time, you should store them that way and even with...

Andrew-495157 (9/7/2012)

---

Thanks to all.

BUT - the issue is not conversion per se. I don't need any more info on coercion, casting, conversion etc!

The issue now is - why the code...

You do this with some pretty simple date math. Just use the beginning of the day when comparing.

See this article from Lynn about some common datetime routines. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/%5B/url%5D

Andrew-495157 (9/7/2012)

---

OK

When SQL compares column values with a variable value where the values are diferent data types.

There are 2 posibel methods:

1 Coerce each column value as it is read row...

I think is the one Lynn was thinking of.

http://msdn.microsoft.com/en-us/library/ms190309.aspx

Implicit conversions, which is what you have when datatypes don't match (like int and varchar) will always attempt to use the higher...

Your where clause has rendered this nonSARGable. You don't need to convert your date to a formatted date string for this...

ChrisM@Work (9/7/2012)

---

Have a look in BOL under CONVERT to see...

Hi and welcome to SSC.

This can certainly be done but in order to help you need to first help us. You will need to post ddl (create table scripts),...

bitbucket-25253 (9/7/2012)

---

Clicking on the above link returned 111...

And to add to Luis, there is a reason we have a datetime datatype in sql. Please us it and not some horrible integer/string disaster. It will cause you and...

+1 😀

Good question. Made me stop and think before my first...

Here is my take on what you need to do. You should read the article at the first link in my signature, the one about best practices. Keep in mind...

I saw that post and got very quiet as I...