lina saleh wrote:BETWEEN is inclusive of the start and end values provided.user-Keen wrote:if i use BETWEEN keyword, date 01-JAN-rrrr and 31-dec-rrrr will be included or not?
Run this query, it will get you desire output.
select * from PRL_SEMAK_LAYAK
where trkh_semak_layak BETWEEN TO_DATE('01-JAN'||2011,'DD-MON-RRRR') AND TO_DATE('31-DEC'||2013,'DD-MON-RRRR')
That means you'll get all records from 01-JAN-2011 regardless of their time, but on records actually at 00:00:00 from the 31st December 2013 because all other times are greater than the value specified.
WHERE trkh_semak_layak >= to_date('01-JAN-2011 00:00:00','DD-MON-YYYY HH24:MI:SS') AND trkh_semak_layak <= to_date('31-DEC-2013 00:00:00','DD-MON-YYYY HH24:MI:SS')
or use the following day minus one second...
WHERE trkh_semak_layak BETWEEN to_date('01-JAN-2011 00:00:00','DD-MON-YYYY HH24:MI:SS') AND to_date('31-DEC-2013 23:59:59','DD-MON-YYYY HH24:MI:SS')
Note: when using 4 digit years, it's not necessary to use the RRRR format. RR is typically used for those badly designed or old systems where there are only 2 digit years provided. Just use YYYY, as it's more readable that it's the year.
WHERE trkh_semak_layak BETWEEN to_date('01-JAN-2011','DD-MON-YYYY') AND to_date('1-JAN-2014','DD-MON-YYYY')-(1/(24*60*60)) -- you can use 1/86400 if you want as that is the same and equals 1 second