My Sql Date comparision including given date range

Question

I have date stored as updatedOn (values like '2012-11-12 12:38:43')

My queries is:

select * from mytable 
where updatedOn >= '11/12/2012' AND updatedOn <= '11/12/2012'

My goal is to get all records inclusive given from & to dates

Answer 1

If you avoid <= and use < instead, you end up being forced to use logic that works regardless of whether or not your data has a time component.

SELECT
  *
FROM
  myTable
WHERE
      updatedOn >= '2012-12-11'
  AND updatedOn <  '2012-12-11' + INTERVAL '1' DAY

Answer 2

You need to take the DATE() component of your updatedOn column and format your date literals in one of the formats supported by MySQL:

SELECT *
FROM   mytable
WHERE  DATE(updatedOn) BETWEEN '2012-12-11'
                           AND '2012-12-11'

Or else, use MySQL's STR_TO_DATE() function to convert your strings to MySQL dates:

SELECT *
FROM   mytable
WHERE  DATE(updatedOn) BETWEEN STR_TO_DATE('11/12/2012', '%d/%m/%Y')
                           AND STR_TO_DATE('11/12/2012', '%d/%m/%Y')