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.

Solution

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

Comments

[PM 77-1]

Look into DATEPART function. See msdn.microsoft.com/en-us/library/aa258265(v=sql.80).aspx

[Adir D]

@PM77-1 did you read the question?

[PM 77-1]

Well... I did. Made a mistake. Meant CONVERT.

[Adir D]

@PM77-1 not sure how that helps, either. Maybe read the question one more time?

[user576510]

thanks for attending my question. What do you mean by "stop thinking about a format" ? Do you mean for a stored date for sql server it is same if I entered date for matching 1/2/1900 or 2/1/1900 or 1900/2/1 and even 1900/1/2 ? Can you please clarify it.

[Adir D]

I mean that SQL Server does not store your date in the human readable format that you think it does. It stores the date internally as two integers representing the day and time respectively. When you pass a string to SQL Server, the interpretation depends on regional settings, language settings, dateformat settings, etc. So if you store January 2nd in the database, using 1/2/1900 might find your row, it might not. Using 2/1/1900 might find your row, it might not. These depend on various settings (for more info, read the link). If you use 19000102 there will never be any confusion...

[user576510]

1. In 19000102, 01 is month and 02 is date right ? 2. Will it still consider time part in date or not in matching ? 3. Do you mean 19000102 is always safe to do no matter what ever regional settings are on server ? Thanks a lot for clarifying all these

[Adir D]

In 19000102, yes, 01 is month and 02 is day. YYYYMMDD, always. It won't consider time. In the example you gave in the question, it looks like you are using 19000101 as a "magic" value, so it should not contain time. If you need to find all the values for a specific day, and time is included, you need to use a range query (or convert to DATE first if using SQL Server 2008). If you don't care about the time and are using SQL Server 2008, use DATE for the column in the first place. And yes, once again, with YYYYMMDD it will always interpret that as YYYYMMDD regardless of regional settings.

[PhilHibbs]

If I specify a literal like '2015-08-28' it says that it's out of range, how does it decide what the 08 and the 28 mean? Clearly it thinks I am using YYYY-DD-MM which is just bizarre. I assumed that year-first dates would be interpreted as ANSI format.

[Adir D]

@PhilHibbs No, they're not. You need to use yyyymmdd without dashes, otherwise it is open to more liberal interpretation. For example, try: SET LANGUAGE FRENCH; DECLARE @d1 DATETIME = '19990102', @d2 DATETIME = '1999-01-02'; SELECT DATENAME(MONTH, @d1), DATENAME(MONTH, @d2); and see sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/…