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'
LVL 36
Loganathan NatarajanLAMP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kingjelyCommented:

Hi please try

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

kingjelyCommented:

Try this

SELECT * FROM `tbl_competition`
WHERE cpt_sdate >=  '2010-10-18'
AND  cpt_edate = '2010-10-22'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

SnarflesCommented:
SELECT * FROM `tbl_competition` WHERE cpt_sdate between "2010-10-18" and "2010-10-22";
SnarflesCommented:
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.
kingjelyCommented:

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.



Loganathan NatarajanLAMP DeveloperAuthor Commented:
@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..

kingjelyCommented:
And what if you take out the limit 0,30 ?
kingjelyCommented:
same huh !
kingjelyCommented:
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

Loganathan NatarajanLAMP DeveloperAuthor Commented:
No, it is only date data type.
kingjelyCommented:
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 !!  )
kingjelyCommented:

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

Loganathan NatarajanLAMP DeveloperAuthor Commented:
thanks, it worked out.

SELECT *
FROM `tbl_competition`
WHERE cpt_sdate >= '2010-10-20'
AND cpt_edate <= '2010-10-22'
LIMIT 0 , 30
kingjelyCommented:
cool no probs.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.