Query to select data between two dates with the format m/d/yyyy

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

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

Answer 2

Try this

SELECT * 
FROM xxx 
WHERE dates BETWEEN STR_TO_DATE('10/10/2012', '%m/%d/%Y') 
  AND STR_TO_DATE('10/12/2012', '%m/%d/%Y')  ;

or

SELECT * 
FROM xxx 
WHERE STR_TO_DATE(dates , '%m/%d/%Y') BETWEEN STR_TO_DATE('10/10/2012', '%m/%d/%Y') 
  AND STR_TO_DATE('10/12/2012', '%m/%d/%Y')  ;

Answer 3

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

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

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

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 7

you have to split the datetime and then store it with your desired format like dd/MM/yyyy. then you can use this query with between but i have objection using this becasue it will search every single data on your database,so i suggest you can use datediff.

        Dim start = txtstartdate.Text.Trim()
        Dim endday = txtenddate.Text.Trim()
        Dim arr()
        arr = Split(start, "/")
        Dim dt As New DateTime
        dt = New Date(Val(arr(2).ToString), Val(arr(1).ToString), Val(arr(0).ToString))
        Dim arry()
        arry = Split(endday, "/")
        Dim dt2 As New DateTime
        dt2 = New Date(Val(arry(2).ToString), Val(arry(1).ToString), Val(arry(0).ToString))

        qry = "SELECT * FROM [calender] WHERE datediff(day,'" & dt & "',[date])>=0 and datediff(day,'" & dt2 & "',[date])<=0 "

here i have used dd/MM/yyyy format.

Answer 8

use this

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

you are entering string, So give the name of month as according to format...

Answer 9

Try this:

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

Answer 10

DateTime dt1 = this.dateTimePicker1.Value.Date;
DateTime dt2 = this.dateTimePicker2.Value.Date.AddMinutes(1440);
String query = "SELECT * FROM student WHERE sdate BETWEEN '" + dt1 + "' AND '" + dt2 + "'";