Link to home
Start Free TrialLog in
Avatar of Loganathan Natarajan
Loganathan NatarajanFlag for India

asked on

MySQL - select records between two diff. date fields ?

I want to select records between these two date fields. it does not display records...

SELECT * FROM `tbl_competition` WHERE cpt_sdate = '2010-10-18'      AND cpt_edate = '2010-10-22'
Avatar of kingjely
kingjely
Flag of Australia image


Hi please try

SELECT * FROM `tbl_competition`
 WHERE cpt_sdate
BETWEEN  '2010-10-18'  AND cpt_edate = '2010-10-22'
BLEH, sorry didnt see the 2 diff dates..

ASKER CERTIFIED SOLUTION
Avatar of kingjely
kingjely
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SELECT * FROM `tbl_competition` WHERE cpt_sdate between "2010-10-18" and "2010-10-22";
Reason: You can't use a straight = >= <= because they are for comparing integers (well '=' can be used on strings...) and your dates are not integers so you must use 'between' for it to work.

Have you tested what you have posted snarfels?
1) there are 2 date fields not 1. We are not comparing between startdate and startdate.
2) using ' not  "   so yes you can use as i have given, It will work fine.



Avatar of Loganathan Natarajan

ASKER

@kingjely

i tried your sql,

SELECT *
FROM `tbl_competition`
WHERE cpt_sdate >= '2010-10-20'
AND cpt_edate = '2010-10-22'
LIMIT 0 , 30

it shows only ends with 2010-10-22 ... but there are more records in between why it does not show..

And what if you take out the limit 0,30 ?
same huh !
try to expand the date time field.

SELECT *
FROM `tbl_competition`
WHERE cpt_sdate >= '2010-10-19 '
AND cpt_edate = '2010-10-23 '
LIMIT 0 , 30

Do you then get the records you expect?

If so it may be you need to ad the time in also
as the field maybe a datetime field ?


SELECT *
FROM `tbl_competition`
WHERE cpt_sdate >= '2010-10-20 00:00:00'
AND cpt_edate = '2010-10-22 00:00:00'
LIMIT 0 , 30

No, it is only date data type.
I have tested on data here, and it seems to work fine.

Are you looking for between
the 18th and the 22nd     like your origional question, or
the 20th and the 22nd?


(P.s. How come you have all those bands on your shield, is this a trick question !!  )

What if you modify the dates? do you get the correct info?

SELECT * FROM `tbl_competition`
WHERE cpt_sdate >= '2010-10-17 '
AND cpt_edate = '2010-10-23 '
LIMIT 0 , 30

thanks, it worked out.

SELECT *
FROM `tbl_competition`
WHERE cpt_sdate >= '2010-10-20'
AND cpt_edate <= '2010-10-22'
LIMIT 0 , 30
cool no probs.