1 2 Previous Next 18 Replies Latest reply: Feb 27, 2013 2:47 AM by BluShadow Go to original post RSS
      • 15. Re: how to get values between two years??
        You keep where_clause un-change except year value which will come through your paramter or what ever you logic.

        just only change year value to get your desire result.
        • 16. Re: how to get values between two years??
          lina saleh
          But the values of 1st jan & 31st dec will be excluded if I use the where clause that be given to me.In my case, I want both dates 1st jan and 31st dec also must be included in the records that i want.
          • 17. Re: how to get values between two years??
            lina saleh wrote:
            user-Keen wrote:
            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')
            if i use BETWEEN keyword, date 01-JAN-rrrr and 31-dec-rrrr will be included or not?
            BETWEEN is inclusive of the start and end values provided.

            However, with DATEs you should bear in mind that Oracle always includes the time portion in any DATE datatype value, and the default, if it's not specified, is 00:00:00

            So in your above query you are looking for any records where trkh_semak_layak is between:

            01-JAN-2011 00:00:00


            31-DEC-2013 00:00:00


            It's the same as saying:
            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')
            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.

            If you want to be wholly inclusive, you either need to be specific with the times as well:
            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')
            or use the following day minus one second...
            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
            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.
            • 18. Re: how to get values between two years??
              where trkh_semak_layak BETWEEN trunc(dari,'RRRR') AND (ADD_MONTHS(trunc(hingga,'RRRR'),12)-1)
              1 2 Previous Next