Menu
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
368
Here we will see, SQL Query to compare two dates. This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.
To find the difference between dates, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument defines the part of the date/datetime in which you'd like to express the difference. Its value can be year , quarter , month , day , minute , etc.
The methods used for date comparison varies across SQL database servers. But usually, there is a basic procedure for it. If the date is not already in the date format, then use data type conversion functions and convert it from string to DATE data type and then make the relevant comparison.
In this article, we will see the SQL query to check if DATE is greater than today's date by comparing date with today's date using the GETDATE() function. This function in SQL Server is used to return the present date and time of the database system in a 'YYYY-MM-DD hh:mm: ss. mmm' pattern.
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.
125
Try like this
select id,numbers_from,created_date,amount_numbers,SMS_text from Test_Table where created_date <= '2013-12-04' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
