Loganathan Natarajan
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'
SELECT * FROM `tbl_competition` WHERE cpt_sdate = '2010-10-18' AND cpt_edate = '2010-10-22'
BLEH, sorry didnt see the 2 diff dates..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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..
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
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
ASKER
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 !! )
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
ASKER
thanks, it worked out.
SELECT *
FROM `tbl_competition`
WHERE cpt_sdate >= '2010-10-20'
AND cpt_edate <= '2010-10-22'
LIMIT 0 , 30
SELECT *
FROM `tbl_competition`
WHERE cpt_sdate >= '2010-10-20'
AND cpt_edate <= '2010-10-22'
LIMIT 0 , 30
cool no probs.
Hi please try
SELECT * FROM `tbl_competition`
WHERE cpt_sdate
BETWEEN '2010-10-18' AND cpt_edate = '2010-10-22'