Question of the month April 2016 – What is the result of select $ _5a?

Can you guess the output of this query without actually running it?

SELECT $ _5a

The output is one of the following

1  invalid pseudocolumn "$ _5a".
2 _5a
3 Incorrect syntax near _5a
4 0.0


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

Validating Leap year using ISDATE function

There can be several methods of validating a Leap year (which has 366 days) but using ISDATE function is one of the methods.

Consider the following code

declare @year smallint
set @year=2000
select isdate(cast(@year as char(4))+'0229') as is_leap_year

The result is 1

The idea is to create a date value of 29th February of that year and see if it is a valid date using ISDATE function. If ISDATE returns 1 then it is a Leap Year if it returns 0, it is not a Leap Year