This discussion is archived
5 Replies Latest reply: Oct 30, 2012 5:53 PM by 971542 RSS

Where with to_date returms only one month data

971542 Newbie
Currently Being Moderated
SELECT col1,col2 FROM bill WHERE
trunc (TO_DATE(time_stamp, 'yyyy/mm/dd HH:MI:SS AM') = trunc (TO_DATE('2012/01','yyyy/mm');

This only returns data for first day of the month and need data for a month.
  • 1. Re: Where with to_date returms only one month data
    sb92075 Guru
    Currently Being Moderated
    user2002208 wrote:
    SELECT col1,col2 FROM bill WHERE
    trunc (TO_DATE(time_stamp, 'yyyy/mm/dd HH:MI:SS AM') = trunc (TO_DATE('2012/01','yyyy/mm');

    This only returns data for first day of the month and need data for a month.
    what datatype is TIME_STAMP
  • 2. Re: Where with to_date returms only one month data
    971542 Newbie
    Currently Being Moderated
    char (14) and sample data is 20120204103342 and 20120204152550
  • 3. Re: Where with to_date returms only one month data
    sb92075 Guru
    Currently Being Moderated
    user2002208 wrote:
    char (14) and sample data is 20120204103342 and 20120204152550
    FLAWED, unprofessional & totally amateur "design"!

    NEVER store DATE in CHAR, VARCHAR2, or NUMBER column!

    SELECT col1,col2 FROM bill WHERE substr(time_stamp,1,6) = '201201';
  • 4. Re: Where with to_date returms only one month data
    rp0428 Guru
    Currently Being Moderated
    >
    SELECT col1,col2 FROM bill WHERE
    trunc (TO_DATE(time_stamp, 'yyyy/mm/dd HH:MI:SS AM') = trunc (TO_DATE('2012/01','yyyy/mm');

    This only returns data for first day of the month and need data for a month.
    >
    I answered that question in the other forum.

    Well a TRUNC without a format string only gives you one day. If you want to trunc to the month you need a 'MM' format string.
    SELECT sysdate, trunc(sysdate), trunc(sysdate, 'MM'), 
    trunc(TO_DATE('2012/01','yyyy/mm')) td from dual
     
    SYSDATE,TRUNC(SYSDATE),TRUNC(SYSDATE,'MM'),TD
    10/30/2012 6:48:53 PM,10/30/2012,10/1/2012,1/1/2012
    And if this is true ' sample data is 20120204103342 and 20120204152550 '

    Then this won't work because the format string doesn't match your sample data
    TO_DATE(time_stamp, 'yyyy/mm/dd HH:MI:SS AM')
    And if that did work the TRUNC still only gives you the day
    SELECT trunc (TO_DATE('20120204103342', 'yyyymmddHHMISS')) from dual
    
    TRUNC(TO_DATE('20120204103342','YYYYMMDDHHMISS'))
    2/4/2012
  • 5. Re: Where with to_date returms only one month data
    971542 Newbie
    Currently Being Moderated
    You rock... It worked.
    I am trying report on 10 year old application. I can not change anything.

    Thank you very much for help...

Legend

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