This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Feb 27, 2013 12:47 AM by BluShadow RSS

how to get values between two years??

lina saleh Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points