sql server – Query comparing dates in SQL

sql server – Query comparing dates in SQL

Instead of 2013-04-12 whose meaning depends on the local culture, use 20130412 which is recognized as the culture invariant format.

If you want to compare with December 4th, you should write 20131204. If you want to compare with April 12th, you should write 20130412.

The article Write International Transact-SQL Statements from SQL Servers documentation explains how to write statements that are culture invariant:

Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers, should use the unseparated numeric strings. For example, yyyymmdd as 19980924.

EDIT

Since you are using ADO, the best option is to parameterize the query and pass the date value as a date parameter. This way you avoid the format issue entirely and gain the performance benefits of parameterized queries as well.

UPDATE

To use the the the ISO 8601 format in a literal, all elements must be specified. To quote from the ISO 8601 section of datetimes documentation

To use the ISO 8601 format, you must specify each element in the format. This also includes the T, the colons (:), and the period (.) that are shown in the format.

… the fraction of second component is optional. The time component is specified in the 24-hour format.

Try like this

select id,numbers_from,created_date,amount_numbers,SMS_text 
from Test_Table
where 
created_date <= 2013-12-04

sql server – Query comparing dates in SQL

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

Leave a Reply

Your email address will not be published.