1

I have time stamp values such as 30/05/06 11:40:34.000000000 AM in the column. I need to extract Year (i.e. 2006) and the month (i.e. 05) from this value. Below is my SQL.

SELECT EXTRACT(YEAR FROM TO_DATE('19/10/09 10:45:44.000000000 AM')) FROM DUAL;

After running above SQL, I get an error APPROVED_PROPOSALS.

So, I tried to provide the format as below,

SELECT EXTRACT(YEAR FROM TO_DATE('19/10/09 10:45:44.000000000 AM','HH12:MI:SS.FF')) FROM DUAL;

However, still getting some errors. I am pretty sure that, the format I am providing is not correct.

ORA-01821: date format not recognized

Please help me, to get Year and Month form such values.

Kiran.

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
Kiran Bandal
  • 15
  • 3

4 Answers4

2

You need to use TO_TIMESTAMP instead of TO_DATE.

SELECT EXTRACT (
      YEAR FROM TO_TIMESTAMP ('19/10/09 10:45:44.000000000 AM',
                              'DD/MM/YY HH12:MI:SS.FF9 AM'))
FROM DUAL;

This is assuming year comes after month. In the format part, you need to give the format of date too, not just time part.

Nitish
  • 1,686
  • 8
  • 23
  • 42
0

The problem I see is you don't specify 'dd/mm/yy' in your date format. And as Nitish points out, you have a timestamp (more accurate), not a date.

You should use correct format, as follows:

SELECT EXTRACT(YEAR FROM TO_TIMESTAMP('19/10/09 10:45:44.00000 AM', 'dd/mm/yy HH12:MI:SSXFF PM')) FROM DUAL;

Or us fixed determination:

select substr('19/10/09 10:45:44.00000 AM', 4,2) MONTH 
     , substr('19/10/09 10:45:44.00000 AM', 7,2) YEAR
from dual;
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
0

If you are planning to use timestamp column in your query you won't have problems you mentioned (this example is based on Nitish's answer):

with t as
(
  select to_timestamp('19/10/2009 10:45:44.000000000 am', 'dd/mm/yyyy hh:mi:ss.ff9 am') ts
    from dual
)
select extract(year from ts) ts_year,
       extract(month from ts) ts_month
  from t;

Additionally I would suggest you to check if fractional seconds are stored in this column, since in your examples they are not. This usually means that you are inserting sysdate in this column, instead of systimestamp. If fractional seconds are not needed there is no point in having timestamp column - date type would have been sufficient.

Goran Stefanović
  • 308
  • 1
  • 5
0

Thank you guys. It was a great help, appreciate it . I used the following queries,

  1. Get year :

    select EXTRACT(YEAR FROM(TO_DATE(TO_CHAR(TO_TIMESTAMP ('15/10/09 11:07:28.999900 AM', 'DD/YY/MM HH:MI:SS.FF a.m.'),'DD/YY/MM HH24:MI:SS'), 'DD/YY/MM HH24:MI:SS'))) DT FROM DUAL;

  2. Month

    select EXTRACT(MONTH FROM(TO_DATE(TO_CHAR(TO_TIMESTAMP ('15/10/09 11:07:28.999900 AM', 'DD/YY/MM HH:MI:SS.FF a.m.'),'DD/YY/MM HH24:MI:SS'), 'DD/YY/MM HH24:MI:SS'))) DT FROM DUAL;

Kiran Bandal
  • 15
  • 3