5 Replies Latest reply: Jan 17, 2013 3:31 AM by Bawer RSS

    Counting records between specific year

    Christy H.
      Hello,
      SQL> desc news
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       NEWS_ID                                   NOT NULL NUMBER(14)
       NEWS_DATE                                          TIMESTAMP(0)
       SL_ID                                              NUMBER(2)
       HEADING                                            VARCHAR2(3120)
       DESCRIPTION                                        VARCHAR2(3900)
      I want to delete news that are published before 2012. I tried like this:
      SQL> select count(*) from news where news_date <  news_date('yyyy', '2012');
      select count(*) from news where news_date <  news_date('yyyy', '2012')
                                                   *
      ERROR at line 1:
      ORA-00904: "NEWS_DATE": invalid identifier
      please advise

      Thanks in anticipation
        • 1. Re: Counting records between specific year
          Mihael
          I want to delete news that are published before 2012. I tried like this:
          SQL> select count(*) from news where news_date <  news_date('yyyy', '2012');
          select count(*) from news where news_date <  news_date('yyyy', '2012')
          *
          ERROR at line 1:
          ORA-00904: "NEWS_DATE": invalid identifier
          use "< to_date(...)"
          • 2. Re: Counting records between specific year
            Bawer
            where TO_CHAR(news_date,'YYYY') <  2012;
            • 3. Re: Counting records between specific year
              Christy H.
              Thank you very much
              • 4. Re: Counting records between specific year
                Frank Kulash
                Christy H. wrote:
                Hello,
                SQL> desc news
                Name                                      Null?    Type
                ----------------------------------------- -------- ----------------------------
                NEWS_ID                                   NOT NULL NUMBER(14)
                NEWS_DATE                                          TIMESTAMP(0)
                SL_ID                                              NUMBER(2)
                HEADING                                            VARCHAR2(3120)
                DESCRIPTION                                        VARCHAR2(3900)
                I want to delete news that are published before 2012. I tried like this:
                SQL> select count(*) from news where news_date <  news_date('yyyy', '2012');
                select count(*) from news where news_date <  news_date('yyyy', '2012')
                *
                ERROR at line 1:
                ORA-00904: "NEWS_DATE": invalid identifier
                News_date is a column, not a function.

                You can get the results you want using the TO_TIMESTAMP function:
                WHERE   news_date  < TO_TIMESTAMP ('2012', 'YYYY')
                or a TIMESTAMP literal:
                WHERE   news_date  < TIMESTAMP '2012-01-01 00:00:00'
                 

                By the way, it's a lot more convenient to work with DATEs than with TIMESTAMPs. DATEs are a tiny bit faster, and require less storage space. Why not use the DATE datatype instead of TIMESTAMP (0)?
                • 5. Re: Counting records between specific year
                  Bawer
                  if you work with dates, it is better to compare with dates.
                  where TO_CHAR(news_date,'YYYY') <  2012;
                  this converts all news_date column to a varchar, than again to number (implicit) to compare with 2012. If you have an index on this column, it won't help you (because you don't query just the column, you queries a new column generated through to_char function. it causes a full table scan).

                  but if you write it as follows
                  where news_date < TO_DATE('01.01.2012','DD.MM.YYYY') ;
                  it converts your input (string) to date only once, than compares with all news_date columns. in big tables, this can be more efficient (if you have an index on this column).