More on extracting year from date - Oracle

Question

I have a question about selecting year from a date. This is in Oracle database 12c.

Given that SELECT trunc(SYSDATE) FROM DUAL; returns 02/06/2020

These work proper and return current year of 2020 -

SELECT EXTRACT(YEAR FROM trunc(SYSDATE)) FROM DUAL;
SELECT TO_CHAR(trunc(SYSDATE,'YYYY')) FROM DUAL;

These do not work and give error -

SELECT EXTRACT(YEAR FROM '02/06/2019') FROM DUAL;

Gives error: ORA-30076: invalid extract field for extract source

SELECT TO_CHAR('02/06/2019','YYYY') FROM DUAL;

Gives error: ORA-01722: invalid number

The same format is being passed with sysdate and hard coded date of 02/06/2019. Why is it that one works and the other does not?

I know I could just select 2019 from dual but that is not the point or use case here.

score 1 · Accepted Answer · answered Feb 06 '20 at 20:08

1

You can't extract year from a string (which '02/06/2019' is). First convert it to date:

SQL> SELECT EXTRACT(YEAR FROM to_date('02/06/2019', 'dd/mm/yyyy')) year FROM DUAL;

      YEAR
----------
      2019

SQL>

Or, if you know that last 4 digits are valid year, then

SQL> select substr('02/06/2019', -4) year from dual;

YEAR
----
2019

SQL>

answered Feb 06 '20 at 20:08

Littlefoot

score -1 · Answer 2 · answered Feb 06 '20 at 20:08

-1

It comes down to the data type being passed. sysdate by default is a DATE field. A hard date like '02/06/2020' by default is considered a string.

To get around that, just cast the string as a date. All good.

SELECT TO_CHAR(cast('6-feb-2019' as date),'YYYY') FROM DUAL;

answered Feb 06 '20 at 20:08

alexherm

This does not always work [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=964fd47cd8e7e0ae2e212a138220bfb1). If you want to convert a string to a date then use `TO_DATE` with an explicit format model and don't rely on implicit conversions through session parameters. – MT0 Feb 06 '20 at 22:06
What is the difference between ``to_date()``` and ```cast(xxx as date)```? – alexherm Feb 06 '20 at 22:14
2

`TO_DATE` takes a string argument (and optional format model and NLS settings parameters) and converts it to a date data type. `CAST( string_value AS DATE )` will perform an implicit string-to-date conversion using the current session's NLS parameters. A user can change their own session parameters at **ANY** time so you should never rely on them and the default session values will [depend on the territory](https://stackoverflow.com/a/50164234/1509264) set when creating the database so you cannot rely on them being consistent in an international setting. – MT0 Feb 06 '20 at 22:19

2 Answers2