Rahul Gautam
Rahul Gautam

Reputation: 1121

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

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.

Upvotes: 4

Views: 116513

Answers (10)

Bipul Roy
Bipul Roy

Reputation: 163

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.

Upvotes: 0

S M Jobayer Alam
S M Jobayer Alam

Reputation: 238

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 :)

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

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)

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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'

enter image description here

OR

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

enter image description here

Demo on SQLFiddle

Upvotes: 7

ShadowUC
ShadowUC

Reputation: 754

$Date3 = date('y-m-d');
$Date2 = date('y-m-d', strtotime("-7 days"));
SELECT * FROM disaster WHERE date BETWEEN '".$Date2."' AND  '".$Date3."'

Upvotes: 1

user3600122
user3600122

Reputation: 1

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 + "'";

Upvotes: -2

Ankyy
Ankyy

Reputation: 1

Try this:

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

Upvotes: -2

user2001117
user2001117

Reputation: 3777

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')  ;

Upvotes: 2

user2001117
user2001117

Reputation: 3777

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.

Upvotes: 0

namo_rocks
namo_rocks

Reputation: 1

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...

Upvotes: -2

Related Questions