Reputation: 3185

Query comparing dates in SQL

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

Upvotes: 116

Answers (12)

Earl Ruby

Reputation: 1570

Test_Table.created_date is a date/time type field.

'2013-04-12' is a string constant.

To compare two values they need to be the same type. The efficient way to do that is to convert the string constant to a date/time constant using a function. (It's far more efficient to convert a constant once and compare that against the stored value than to convert every single stored value a string so you can compare it to another string.)

What that function is depends on what database you're using. For instance, I am using an Apache Impala database, and it uses TO_TIMESTAMP() to convert a string to a timestamp:

select id,numbers_from,created_date,amount_numbers,SMS_text 
from Test_Table
where 
created_date <= to_timestamp('2013-04-12', 'yyyy-MM-dd')

Oracle and Postgres also use to_timestamp() but they use different date format strings. Oracle and Postgres would use 'YYYY-MM-DD' in the above example.

MySQL and MariaDB use the STR_TO_DATE() function and an entirely different way to specify date formats.

In general just Google "my database name" + "string to date format" and you'll find the date function and date format for your database.

Upvotes: 1

Bimzee

Reputation: 1246

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',23)

This conversion is also applicable during using GetDate() function

-- Updating cast number as per the suggestion of @Thomas853

Upvotes: 17

Hardboiled24

Reputation: 1

For my queries on MS Access, I can compare dates with this syntax:

SELECT COUNT(orderNumber) AS Total
FROM Orders
WHERE orderDate >=#2003/04/01#
AND orderDate <=#2003/06/30#;

Where the output is the number of orders between 2003-04-01 and 2003-06-30.

Upvotes: -1

karthik kasubha

Reputation: 424

Below query can be used to find the records of month November for the year 2013.

Select id,numbers_from,created_date,amount_numbers,SMS_text 
from Test_Table
where Month(created_date) = 11 and Year(created_date) = 2013

Upvotes: 1

Rahul Gupta

Reputation: 24

you can also use to_char(column_name, 'YYYY-MM-DD) to change format

Upvotes: -1

Pytharas

Reputation: 21

Convert them to dates in the same format and then you can compare. Τry like this:

where convert(date, created_date,102) <= convert(date,                                  /*102 is ANSI format*/
                                                    substring('2013-04-12',1,4) + '.' + /*year*/
                                                    substring('2013-04-12',9,2) + '.' + /*month*/
                                                    substring('2013-04-12',6,2)         /*day*/
                                                ,102)

Upvotes: 2

Ren Marín

Reputation: 9

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 <= #2013-04-12#

Upvotes: 0

Panagiotis Kanavos

Reputation: 131364

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 4^th, you should write '20131204'. If you want to compare with April 12^th, 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.

Upvotes: 117

Kuzgun

Reputation: 4737

You put <= and it will catch the given date too. You can replace it with < only.

Upvotes: 5

Naresh Pansuriya

Reputation: 2045

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))  <= convert(datetime,'2013-04-12')

Upvotes: 5

Nithesh Narayanan

Reputation: 11765

Try like this

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

Upvotes: 38

Bowie

Reputation: 1002

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.

Upvotes: 1

Query comparing dates in SQL

Answers (12)

Related Questions