Query to select data between two dates with the fo

Question

I am facing problem when i'm trying to select records from a table between two dates.

m using the following query

select * from xxx where dates between '10/10/2012' and '10/12/2012'

this query works for me but when the dates are in format like 1/1/2013.. it doesn't work..

plz solve my problem ASAP.

Answer 1

Try this:

select * from xxx where dates between convert(datetime,'10/10/2012',103) and convert(dattime,'10/12/2012',103)

Answer 2

$Date3 = date('y-m-d');
$Date2 = date('y-m-d', strtotime("-7 days"));

SELECT * FROM disaster WHERE date BETWEEN '".$Date2."' AND  '".$Date3."'

Answer 3

SELECT * FROM tablename WHERE STR_TO_DATE(columnname, '%d/%m/%Y')
  BETWEEN STR_TO_DATE('29/05/2017', '%d/%m/%Y')
    AND STR_TO_DATE('30/05/2017', '%d/%m/%Y')

It works perfectly :)

Answer 4

By default Mysql store and return ‘date’ data type values in “YYYY/MM/DD” format. So if we want to display date in different format then we have to format date values as per our requirement in scripting language

And by the way what is the column data type and in which format you are storing the value.

Answer 5

select * from xxx where dates between '2012-10-10' and '2012-10-12'

I always use YYYY-MM-DD in my views and never had any issue. Plus, it is readable and non equivocal.
You should be aware that using BETWEEN might not return what you expect with a DATETIME field, since it would eliminate records dated '2012-10-12 08:00' for example.
I would rather use where dates >= '2012-10-10' and dates < '2012-10-13' (lower than next day)

Answer 6

This solution provides CONVERT_IMPLICIT operation for your condition in predicate

SELECT * 
FROM xxx 
WHERE CAST(dates AS date) BETWEEN '1/1/2013' and '1/2/2013'

OR

SELECT * 
FROM xxx 
WHERE CONVERT(date, dates, 101) BETWEEN '1/1/2013' and '1/2/2013'

Demo on SQLFiddle