asked on

T-SQL: Why do the single quotes make a difference for a date field?

Hello:

Below is code that I recently used. You will notice that one restriction that I created is where DOCDATE is greater than 09/30/2013.

At first, I did not have quotes around that date. So, even though the database contains no data pertaining to dates greater than 09/30/2013, data was displayed.

When I added single quotes around 09/30/2013, though, no data displayed. Again, that's correct, since there is no data pertaining to dates greater than 09/30/2013.

Why did placing single quotes around the date field make a difference, in terms of the records that would be returned (or not returned)?

Thanks!

John

select DISTINCT CN00500.CUSTNMBR as [GP ID], RM00101.CUSTNAME as [Customer Name],
CN00500.USERDEF1 as [Billing Gp Name], CN00500.USERDEF2 as [Alias]
from CN00500
INNER JOIN RM00101 ON
CN00500.CUSTNMBR = RM00101.CUSTNMBR 
INNER JOIN SOP30200 ON
CN00500.CUSTNMBR = SOP30200.CUSTNMBR
WHERE SOP30200.SOPTYPE = 3 AND SOP30200.DOCDATE > '09/30/2013'

Open in new window

Pawan Kumar

Which version of SQL Server you are using?

it was the bug with the Microsoft. Seems like they have fixed it. Refer below-
https://support.microsoft.com/en-us/kb/268593

I am getting below error when i am running that in SQL SERVER 2012.

/*------------------------
SELECT * FROM PawanDates
WHERE Dates > 12/03/2015
------------------------*/
Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int

Hope it helps!

Pawan Kumar

Adding to the above , one more comment from MS-
Earlier If you pass a value without quotes that is used as a datetime value. That is treated as the default date plus the number of days equal to the computed amount of the assigned value.

Hope it helps!

PortletPaul

Your initial query (without the quotes) involves an "implied conversion" where your date column is being converted to a varchar (using a server default format for that conversion)

By using quotes around the string '09/30/2013' , perhaps ironically, SQL Server will understand that is meant to be a date (i.e. treats it as a "date literal" and returns a datetime value to the predicate

Try this:

declare @docdate datetime = getdate()

select
  case when @docdate > 09/30/2013   then 'no' else 'yes' end
, cast(@docdate as varchar)
, case when @docdate > '20130930' then 'no' else 'yes' end


set @docdate = dateadd(year,-50,getdate())

select
  case when @docdate > 09/30/2013   then 'no' else 'yes' end
, cast(@docdate as varchar)
, case when @docdate > '20130930' then 'no' else 'yes' end
;

+---+------------------+---------------------+------------------+
|   | (No column name) |  (No column name)   | (No column name) |
+---+------------------+---------------------+------------------+
| 1 | no               | Dec 23 2016  5:22AM | no               |
+---+------------------+---------------------+------------------+

+---+------------------+---------------------+------------------+
|   | (No column name) |  (No column name)   | (No column name) |
+---+------------------+---------------------+------------------+
| 1 | no               | Dec 23 2016  5:22AM | yes              |
+---+------------------+---------------------+------------------+

Open in new window

The style MM/DD/YYYY is NOT universally understood and CAN FAIL to convert into a date depending on server settings

THIS is THE safest of all possible date literal formats in SQL Server: 'YYYYMMDD'

PLEASE do NOT assume the server will always understand mm/dd/yyyy - but it will always understand 'yyyymmdd'

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

Pawan
please explain how 09/30/2013
becomes 20,001,012