All records within 3 month access WHERE clause on Date PHP

Question

I want to do a Quater wise search on the records store in the database. The problem is that i don't know how to use BETWEEN in date. Any help would be appreciated.

SELECT * FROM ACED 
         INNER JOIN RACE
         ON ACED.RACE_NO=RACE.RACE_NO

         WHERE R_DATE 
         BETWEEN 
         TO_CHAR(TO_DATE(R_DATE, 'DD-MON-YYYY'), 'MON-YY') = 'JAN-14'
         AND
         TO_CHAR(TO_DATE(R_DATE, 'DD-MON-YYYY'), 'MON-YY') = 'MAR-14'

http://stackoverflow.com/questions/3651985/compare-dates-in-mysql — Hanky Panky, Apr 09 '14 at 17:28

score 2 · Answer 1 · answered Apr 09 '14 at 17:30

2

SELECT * FROM ACED 
         INNER JOIN RACE
         ON ACED.RACE_NO=RACE.RACE_NO
         WHERE   
         TO_CHAR(R_DATE,'Q') = 1;

Q in format model gives you the quarter of the year. Jan to march is 1st Quarter. and so on.

answered Apr 09 '14 at 17:30

Maheswaran Ravisankar

17,652
6
47
69

what is that Q? @OracleUser – user3400389 Apr 09 '14 at 17:31
1

You can refresh the screen and try again. You might not see my updated answer. – Maheswaran Ravisankar Apr 09 '14 at 17:39

Mureinik · Accepted Answer · 2014-04-09T17:36:52.773

1

between works in the same fashion for dates, numbers, strings, etc - You just need to make sure you pass date arguments and not character arguments (which will cause the operator to do a lexicographical comparison). E.g.:

SELECT     * 
FROM       aced
INNER JOIN race ON aced.race_no = race.race_no
WHERE      r_date BETWEEN TO_DATE('01-14','mm-yy') AND 
                          TO_DATE('03-14','mm-yy')

edited Apr 09 '14 at 17:36

answered Apr 09 '14 at 17:31

Mureinik

297,002
52
306
350

its saying `STR_TO_DATE` is a invalid identifier – user3400389 Apr 09 '14 at 17:35
1

for some reason I decided this was a MySQL question - fixed. – Mureinik Apr 09 '14 at 17:37
Okay now no errors. just showing zero results. I will fix that problem. Thanks +1 – user3400389 Apr 09 '14 at 17:39

All records within 3 month access WHERE clause on Date PHP

2 Answers2