CODEWITHSUNDEEP
oracle-database
ℕʘʘḆḽḘ
ℕʘʘḆḽḘ

Reputation: 19375

how to extract the year from a date in Oracle?

I dont understand why the following query does not work

SELECT stringdate, TO_DATE(TO_CHAR(stringdate),'YYYYMMDD') AS mydate,
EXTRACT(YEAR FROM MYDATE) as myyear
FROM data.repo data 
WHERE ROWNUM <10"

It returns ORA-00904: "MYDATE": invalid identifier

However, just running 

SELECT stringdate, TO_DATE(TO_CHAR(stringdate),'YYYYMMDD') AS mydate

gives correctly:

stringdate    MYDATE
20150130 2015-01-30

This drives me crazy. What is the issue? Thanks!

Upvotes: 1

Views: 770

Answers (2)

Kedar Limaye
Kedar Limaye

Reputation: 1041

Select mydate, EXTRACT(YEAR FROM MYDATE) as myyear
From (
    SELECT stringdate, TO_DATE(TO_CHAR(stringdate),'YYYYMMDD') AS mydate
          /* , EXTRACT(YEAR FROM MYDATE) as myyear */
    FROM data.repo data 
    WHERE ROWNUM <10"
 ) A

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35563

The problem is that you CANNOT reference the alias "mydate" in the same select clause.

SELECT
  stringdate
, EXTRACT(YEAR FROM MYDATE) as myyear
FROM (
    SELECT stringdate
    , TO_DATE(TO_CHAR(stringdate),'YYYYMMDD') AS mydate
    FROM data.repo data 
    WHERE ROWNUM <10
    )

There are other possibilities e.g.

SELECT stringdate
    , TO_DATE(TO_CHAR(stringdate),'YYYYMMDD') AS mydate
    , to_number(substr(stringdate,1,4)) AS YR
FROM data.repo data 
WHERE ROWNUM <10

for use of "select *" (nb: not recommend in production coding) in Oracle you need o use the table/subquery aliases, like so:

SELECT
  d.*
, EXTRACT(YEAR FROM MYDATE) as myyear
FROM (
    SELECT data.*
    , TO_DATE(TO_CHAR(stringdate),'YYYYMMDD') AS mydate
    FROM data.repo data 
    WHERE ROWNUM <10
    ) d

Upvotes: 2

Related Questions