Query comparing dates in SQL

Question

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  lt    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

User · Answer

Try like this  select id numbers from created date amount numbers SMS text  from Test Table where  created date  lt    2013-12-04

User · Answer

Try to use     before and after of the date and be sure of your system date format  maybe  YYYYMMDD O YYYY-MM-DD O MM-DD-YYYY O USING    O       Ex    select id numbers from created date amount numbers SMS text   from Test Table  where   created date  lt    2013-04-12

User · Answer

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  lt   convert date  2013-04-12  102    This conversion is also applicable during using GetDate   function

User · Answer

Date format is yyyy-mm-dd  So the above query is looking for records older than 12Apr2013  Suggest you do a quick check by setting the date string to  2013-04-30   if no sql error  date format is confirmed to yyyy-mm-dd

User · Answer

You put  lt   and it will catch the given date too  You can replace it with  lt  only

User · Answer

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 Server s 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 datetime s 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

User · Answer

please try with below query  select id numbers from created date amount numbers SMS text  from Test Table where  convert datetime  convert varchar 10   created date  102     lt   convert datetime  2013-04-12

[sql] Query comparing dates in SQL

Examples related to sql

Examples related to sql-server

Examples related to date

Examples related to compare