Query comparing dates in SQL
Follow the procedure.
Always use date like ‘20130412’. It is recognized as the culture unchanging format .So use that instead of ‘2013-04-12’ which is the meaning of depends on the local culture,
For example, compare with December 4th, we should write ‘20131204’. Similarly compare April 12th, we should write ‘20130412’.
From SQL Server’s documentation a article explains how to write statements that are culture invariant called Write International Transact-SQL Statements:
These applications can be used for other APIs, or Transact-SQL scripts, stored procedures, and triggers, should use the non- separated numeric strings. For example, yyyymmdd can be written as 19980924.
The best way of parameterize the query and sendthe date value as a date parameter, if one using ADO . In this way one can neglet the format issue fully and gain the performance benefits of parameterized queries.
All the elements must be specified, to use the the ISO 8601 format in a verbatim . To quote from the ISO 8601 section of datetime’s documentation.
In the deal with ISO 8601 format, we have to specify each element in the format. This also includes the T, the colons (:), and the period (.) and so on that are shown in the format.
The time component is specified in the 24-hour format.
I have a table with dates that all happened in the month November. I wrote this query
select id,numbers_from,created_date,amount_numbers,SMS_text from Test_Table where created_date <= '2013-04-12'
This query should return everything that happened in month 11 (November) because it happened before the date ‘2013-04-12’ (in December)
But it’s only returning available dates that happened in days lesser than 04 (2013-04-12)
Could it be that it’s only comparing the day part? and not the whole date?
How to fix this?
Created_date is of type date
Date format is by default yyyy-dd-MM
if You are comparing only with the date vale, then converting it to date (not datetime) will work
select id,numbers_from,created_date,amount_numbers,SMS_text from Test_Table where created_date <= convert(date,'2013-04-12',102)
This conversion is also applicable during using GetDate() function