How does SQL Server compare a date to a string literal?

Question

I am modifying a query, here is my script:

select 
CASE When EndDate='1/1/1900' 
THEN NULL 
ELSE EndDate END,*
from Members

This script simply compares date, if it is '1/1/1900' then returns null otherwise it returns the date.

I can see in my database date is stored in the following format:

1900-01-01 00:00:00.000

Question is how SQL Server is matching date like this when my pattern is different from the stored one. Also in date format I am not passing time element.

Answer 1

SQL Server converts the string literal you are passing ('1/1/1900') to a datetime value due to data type precedence (since datetime has higher precedence than string types). If you pass an invalid date as your string, e.g. '2/31/1900', you will get a conversion error (Msg 242) because SQL Server doesn't know what February 31st means. It is not trying to match a string that looks like what you are passing, it converts both to its internal representation for dates (more on that in my comment).

When dealing with dates specifically, stop thinking about a format except that when you pass string literals, m/d/y (or is that d/m/y?) is a terrible format to use. Much safer to use:

YYYYMMDD

Your query should read:

SELECT CASE When EndDate = '19000101' 
 THEN NULL ELSE EndDate END, ...other columns...
FROM dbo.Members;

This way, when you pass a date like September 8th, it is not misinterpreted by SQL Server, other readers, etc. Is 09/08/2013 September 8th or August 9th? Depends on what part of the world you're in, right? In your case it's okay because the day and month are the same, but this won't always be the case. Please see the following article:

• Bad habits to kick : mis-handling date / range queries

(Please, please, please read that link in its entirety.)

Finally, if you are using DATETIME/SMALLDATETIME and are looking for values from a specific day, you should not be using equality at all, but rather a range query. For example, to find all the rows where EndDate falls on April 15th, 2013, regardless of time, you would say:

WHERE EndDate >= '20130415'
  AND EndDate <  '20130416'

(Read this link to understand why you don't want to use BETWEEN here.)

If you are on SQL Server 2008 or better, you can still achieve sargability on this column with CONVERT, but this is a rare exception - usually you don't want to use a function against a column.

WHERE CONVERT(DATE, EndDate) = '20130415'

A couple of other comments - not directly related to your question, but peripheral observations about your code:

1. Always use the schema prefix
2. Never use SELECT * in production