Always specify Date values within single quotes

My coworker told me that a particular query was giving him wrong result despite seeing no logical error in the code. Well when I looked into the code I saw that date value was specified without single quotes. Do you know what happens in this case? Let us explore it

Let us create the following dataset

create table #sales_details(sales_id int identity(1,1), product_id int, sales_date datetime, sales_amount decimal(12,2))

insert into #sales_details(product_id , sales_date , sales_amount )
select 100001,'2014-01-01',3400 union all
select 100002,'2015-11-10',600 union all
select 100001,'2015-10-22',3400 union all
select 100002,'2015-11-30',3400 union all
select 100003,'2016-02-11',220

Now you want to find total sales count and sales amount made from year 2015 to till date

select count(*) as sales_count, sum(sales_amount) as sales_amount from #sales_details
where sales_date>=2015-01-01

Run the above code which returns the following result

sales_count     sales_amount
-----------   ---------------
5                11020.00

Did you notice anything? The above code returns all data because there is no single quotes around the date. So what happens when date value is expressed without single quotes? It becomes an arithmetic expression

2015-01-01 becomes the number 2013 which in turn converted to the date value of 1905-07-07 ie 2013 days are added to the base date 1900-01-01

Because of implicit conversion, it is difficult to find out this error particularly if you have this in a big procedure. When date value is within single quotes, you get correct result

So always make sure that date values are specified within single quotes

Advertisements

2 thoughts on “Always specify Date values within single quotes

  1. I’d like to add that if you are specifying a datetime without any time element you should format the date ‘yyyymmdd’ instead of ‘yyyy-mm-dd’. ‘yyyymmdd’ is the ISO format for dates and ‘yyyy-mm-dd hh:mm:ss’ is the ISO for a date with a time component. I’ve been on SQL Server instances where ‘yyyy-mm-dd’ does not work but ‘yyyymmdd’ does.

  2. Thanks Jonathan Roberts. Yes I am aware of what you said. YYYYMMDD works for both the data types (Date and DATETIME) and if you use hyphenated date values with time, use Time separator ex YYYY-MM-DDTHH:MM:SS which is unambiguous

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s