1 2 Previous Next 18 Replies Latest reply: Feb 27, 2013 12:53 AM by user-Keen RSS

    how to get values between two years??

    lina saleh
      select distinct a.band_no_daftar, a.episod, a.band_nama1, a.penjara,
      to_char(b.trkh_semak_layak,'yyyy') as tahun,
      c.penj_lokasi
      from banduan a, prl_semak_layak b, penjara c
      where a.band_no_daftar =  b.no_daftar(+)
      and  a.episod = b.episod(+)
      and a.penjara = c.penjara_id
      and b.status_layak = 1
      and b.trkh_semak_layak between trunc(to_date(:dari,'yyyy')) and to_date(:hingga,'yyyy')
      group by  c.penj_lokasi, a.band_no_daftar, a.episod, a.band_nama1, a.penjara,
                 to_char(b.trkh_semak_layak,'yyyy')
      order by a.penjara
      how i'm going to retrieve records between two years??for example from year 2011 to 2012.
        • 1. Re: how to get values between two years??
          jeneesh
          and b.trkh_semak_layak >= trunc(to_date(to_char(2011),'yyyy')) 
          and b.trkh_semak_layak < to_date(to_char(2012+1),'yyyy')
          • 2. Re: how to get values between two years??
            lina saleh
            jeneesh wrote:
            and b.trkh_semak_layak >= trunc(to_date(to_char(2011),'yyyy')) 
            and b.trkh_semak_layak < to_date(to_char(2012+1),'yyyy')
            if i do like that, i will get year 2013 include in the records.
            • 3. Re: how to get values between two years??
              jeneesh
              lina saleh wrote:
              jeneesh wrote:
              and b.trkh_semak_layak >= trunc(to_date(to_char(2011),'yyyy')) 
              and b.trkh_semak_layak < to_date(to_char(2012+1),'yyyy')
              if i do like that, i will get year 2013 include in the records.
              Sorry, you are correct, it should be
              and b.trkh_semak_layak >= to_date(to_char(2011)||'0101','yyyymmdd')
              and b.trkh_semak_layak < to_date(to_char(2012+1)||'0101','yyyymmdd')
              Edited by: jeneesh on Feb 27, 2013 12:01 PM
              • 4. Re: how to get values between two years??
                Manik
                jeneesh wrote:
                and b.trkh_semak_layak >= trunc(to_date(to_char(2011),'yyyy')) 
                and b.trkh_semak_layak < to_date(to_char(2012+1),'yyyy')
                select trunc(to_date(to_char(2011),'yyyy')) from dual 
                --gives : 2/1/2011
                Jeenesh I guess it should be like:
                TRUNC (TO_DATE (2011, 'yyyy'), 'yyyy')
                Please check and confirm.

                Cheers,
                Manik.
                • 5. Re: how to get values between two years??
                  789895
                  Hi,

                  You will not as the condition is something like >=2011 and <2013.

                  cheers

                  VT
                  • 6. Re: how to get values between two years??
                    jeneesh
                    Manik wrote:
                    jeneesh wrote:
                    and b.trkh_semak_layak >= trunc(to_date(to_char(2011),'yyyy')) 
                    and b.trkh_semak_layak < to_date(to_char(2012+1),'yyyy')
                    select trunc(to_date(to_char(2011),'yyyy')) from dual 
                    --gives : 2/1/2011
                    Jeenesh I guess it should be like:
                    TRUNC (TO_DATE (2011, 'yyyy'), 'yyyy')
                    Please check and confirm.

                    Cheers,
                    Manik.
                    Yea, its already corrected..Just was in a confusion that TRUNC is used instead of TO_DATE..
                    • 7. Re: how to get values between two years??
                      lina saleh
                      jeneesh wrote:
                      Manik wrote:
                      jeneesh wrote:
                      and b.trkh_semak_layak >= trunc(to_date(to_char(2011),'yyyy')) 
                      and b.trkh_semak_layak < to_date(to_char(2012+1),'yyyy')
                      select trunc(to_date(to_char(2011),'yyyy')) from dual 
                      --gives : 2/1/2011
                      Jeenesh I guess it should be like:
                      TRUNC (TO_DATE (2011, 'yyyy'), 'yyyy')
                      Please check and confirm.

                      Cheers,
                      Manik.
                      Yea, its already corrected..Just was in a confusion that TRUNC is used instead of TO_DATE..
                      wait ya..actually i'm not going to retrieve records between 2011 and 2012..I just give it as example.My problem is I want to retrieve records between two years.
                      • 8. Re: how to get values between two years??
                        Manik
                        You have to use the below query:
                        I got that distinct out as anyway you are grouping at the end.
                          SELECT a.band_no_daftar,
                                 a.episod,
                                 a.band_nama1,
                                 a.penjara,
                                 TO_CHAR (b.trkh_semak_layak, 'yyyy') AS tahun,
                                 c.penj_lokasi
                            FROM banduan a, prl_semak_layak b, penjara c
                           WHERE     a.band_no_daftar = b.no_daftar(+)
                                 AND a.episod = b.episod(+)
                                 AND a.penjara = c.penjara_id
                                 AND b.status_layak = 1
                                 AND b.trkh_semak_layak BETWEEN TRUNC (TO_DATE (:dari, 'yyyy'), 'yyyy')
                                                            AND TRUNC (TO_DATE (:hingga, 'yyyy'),
                                                                       'yyyy')
                        GROUP BY c.penj_lokasi,
                                 a.band_no_daftar,
                                 a.episod,
                                 a.band_nama1,
                                 a.penjara,
                                 TO_CHAR (b.trkh_semak_layak, 'yyyy')
                        ORDER BY a.penjara;
                        Cheers,
                        Manik.
                        • 9. Re: how to get values between two years??
                          user-Keen
                          Add this query in your where_clause, will help you

                          don't change from & to date excpe you desire year value.

                          WHERE date_column BETWEEN TO_DATE('01-JAN'||2012,'DD-MON-RRRR') AND TO_DATE('01-JAN'||2013,'DD-MON-RRRR')
                          • 10. Re: how to get values between two years??
                            lina saleh
                            Manik wrote:
                            You have to use the below query:
                            I got that distinct out as anyway you are grouping at the end.
                            SELECT a.band_no_daftar,
                            a.episod,
                            a.band_nama1,
                            a.penjara,
                            TO_CHAR (b.trkh_semak_layak, 'yyyy') AS tahun,
                            c.penj_lokasi
                            FROM banduan a, prl_semak_layak b, penjara c
                            WHERE     a.band_no_daftar = b.no_daftar(+)
                            AND a.episod = b.episod(+)
                            AND a.penjara = c.penjara_id
                            AND b.status_layak = 1
                            AND b.trkh_semak_layak BETWEEN TRUNC (TO_DATE (:dari, 'yyyy'), 'yyyy')
                            AND TRUNC (TO_DATE (:hingga, 'yyyy'),
                            'yyyy')
                            GROUP BY c.penj_lokasi,
                            a.band_no_daftar,
                            a.episod,
                            a.band_nama1,
                            a.penjara,
                            TO_CHAR (b.trkh_semak_layak, 'yyyy')
                            ORDER BY a.penjara;
                            Cheers,
                            Manik.
                            i already try what u have suggested to me..But if I want to retrieve records between 2011 and 2012 (for example),the records for year 2012 will not display even I have some records for year 2012 in the database.
                            • 11. Re: how to get values between two years??
                              Manik
                              lina saleh wrote:
                              Manik wrote:
                              You have to use the below query:
                              I got that distinct out as anyway you are grouping at the end.
                              SELECT a.band_no_daftar,
                              a.episod,
                              a.band_nama1,
                              a.penjara,
                              TO_CHAR (b.trkh_semak_layak, 'yyyy') AS tahun,
                              c.penj_lokasi
                              FROM banduan a, prl_semak_layak b, penjara c
                              WHERE     a.band_no_daftar = b.no_daftar(+)
                              AND a.episod = b.episod(+)
                              AND a.penjara = c.penjara_id
                              AND b.status_layak = 1
                              AND b.trkh_semak_layak BETWEEN TRUNC (TO_DATE (:dari, 'yyyy'), 'yyyy')
                              AND TRUNC (TO_DATE (:hingga, 'yyyy'),
                              'yyyy')
                              GROUP BY c.penj_lokasi,
                              a.band_no_daftar,
                              a.episod,
                              a.band_nama1,
                              a.penjara,
                              TO_CHAR (b.trkh_semak_layak, 'yyyy')
                              ORDER BY a.penjara;
                              Cheers,
                              Manik.
                              i already try what u have suggested to me..But if I want to retrieve records between 2011 and 2012 (for example),the records for year 2012 will not display even I have some records for year 2012 in the database.
                              How do we know that, we dont have any of your test data, and if you give 2011 and 2012 between includes both the dates...
                              We cannot help unless you present more information regarding your problem.

                              Cheers,
                              Manik.
                              • 12. Re: how to get values between two years??
                                lina saleh
                                CREATE TABLE PRL_SEMAK_LAYAK
                                (
                                  NO_DAFTAR          VARCHAR2(10 BYTE)          NOT NULL,
                                  EPISOD             NUMBER(2)                  NOT NULL,
                                  TL_HUK             NUMBER(1),
                                  TL_KLAS            NUMBER(1),
                                  TL_JAD4            NUMBER(1),
                                  TL_KESLAIN         NUMBER(1),
                                  TL_LP              NUMBER(1),
                                  TL_RAY             NUMBER(1),
                                  TL_PPI             NUMBER(1),
                                  STATUS_LAYAK       NUMBER(1),
                                  TRKH_LAYAK_PAROL   DATE,
                                  KOD_PEJABAT_PAROL  VARCHAR2(10 BYTE),
                                  TRKH_MULA_PRL      DATE,
                                  TRKH_TAMAT_PRL     DATE,
                                  TEMPOH_T           NUMBER(4),
                                  TEMPOH_B           NUMBER(4),
                                  TEMPOH_H           NUMBER(4),
                                  KLASIFIKASI_PRL    NUMBER(1),
                                  TL_ROTAN           NUMBER(1),
                                  PECAHAN_KESALAHAN  VARCHAR2(3 BYTE),
                                  TERIMA_DOSIR       NUMBER(1),
                                  TRKH_DOSIR         DATE,
                                  TRKH_SEMAK_LAYAK   DATE
                                )
                                
                                Insert into prl_semak layak
                                   (NO_DAFTAR, EPISOD, TL_HUK, TL_KLAS, TL_JAD4, TL_KESLAIN, TL_LP, TL_RAY, TL_PPI, STATUS_LAYAK, TRKH_LAYAK_PAROL, TL_ROTAN, TRKH_SEMAK_LAYAK)
                                 Values
                                   ('70201849', 9, 1, 1, 1, 1, 1, 1, 1, 1, TO_DATE('04/06/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, TO_DATE('12/24/2012 10:46:40', 'MM/DD/YYYY HH24:MI:SS'));
                                Insert into prl_semak layak
                                   (NO_DAFTAR, EPISOD, TL_HUK, TL_KLAS, TL_JAD4, TL_KESLAIN, TL_LP, TL_RAY, TL_PPI, STATUS_LAYAK, TRKH_LAYAK_PAROL, TL_ROTAN, TRKH_SEMAK_LAYAK)
                                 Values
                                   ('670500066', 5, 1, 1, 1, 1, 1, 1, 1, 1, TO_DATE('04/22/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, TO_DATE('12/24/2012 11:03:27', 'MM/DD/YYYY HH24:MI:SS'));
                                Insert into prl_semak layak
                                   (NO_DAFTAR, EPISOD, TL_HUK, TL_KLAS, TL_JAD4, TL_KESLAIN, TL_LP, TL_RAY, TL_PPI, STATUS_LAYAK, TRKH_LAYAK_PAROL, TL_ROTAN, TRKH_SEMAK_LAYAK)
                                 Values
                                   ('71000424', 3, 1, 1, 1, 1, 1, 1, 1, 1, TO_DATE('04/26/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, TO_DATE('12/24/2012 11:11:01', 'MM/DD/YYYY HH24:MI:SS'));
                                Insert into prl_semak layak
                                   (NO_DAFTAR, EPISOD, TL_HUK, TL_KLAS, TL_JAD4, TL_KESLAIN, TL_LP, TL_RAY, TL_PPI, STATUS_LAYAK, TRKH_LAYAK_PAROL, TL_ROTAN, TRKH_SEMAK_LAYAK)
                                 Values
                                   ('470900549', 1, 1, 1, 1, 1, 1, 1, 1, 1, TO_DATE('12/20/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, TO_DATE('11/08/2010 09:15:59', 'MM/DD/YYYY HH24:MI:SS'));
                                Insert into prl_semak layak
                                   (NO_DAFTAR, EPISOD, TL_HUK, TL_KLAS, TL_JAD4, TL_KESLAIN, TL_LP, TL_RAY, TL_PPI, STATUS_LAYAK, TRKH_LAYAK_PAROL, TL_ROTAN, TRKH_SEMAK_LAYAK)
                                 Values
                                   ('30906760', 2, 1, 1, 1, 1, 1, 1, 1, 1, TO_DATE('12/02/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, TO_DATE('11/19/2010 08:54:26', 'MM/DD/YYYY HH24:MI:SS'));
                                COMMIT;
                                i give you this sample,table creation and some insert values.So now,what i should do in order to retrieve all these records?
                                • 13. Re: how to get values between two years??
                                  user-Keen
                                  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')
                                  • 14. Re: how to get values between two years??
                                    lina saleh
                                    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?
                                    1 2 Previous Next