Allie Hart Allie Hart - 5 months ago 6
SQL Question

"Not a valid month" when getting a count by comparing timestamps

I am trying to see how many items are older then 3 years. When I use

select count (*) from ae_dt509 where field13 < to_timestamp( '2013-06-15 00:00:00', 'YY-MM-DD HH:MI:SS' )


I get this error:

Error starting at line 1 in command:
select count (*) from ae_dt509 where field13 < to_timestamp( '2013-06-12 00:00:00', 'YY-MM-DD HH:MI:SS' )
Error report:
SQL Error: ORA-01843: not a valid month
01843. 00000 - "not a valid month"
*Cause:
*Action:


I am not sure whats wrong...

EDIT

I changed the code to correct the year:

select count (*) from ae_dt509 where field13 < to_timestamp( '2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )


and I got the same error.

and then changed it by removing the qoutes around the date:

select count (*) from ae_dt509 where field13 < to_timestamp( 2013-06-15 00:00:00, 'YYYY-MM-DD HH24:MI:SS' )


and got

Bind Variable "00" is NOT DECLARED

Answer

The problem appears to be that field13 is not a date or timestamp field.

For example, if I run the following statement on my local server:

select * from (
  select '2013-06-12 00:00:00' as some_fake_date
  from dual
)
where some_fake_date > systimestamp

... I get the same error as you.

To fix your problem, wrap field13 with to_timestamp and the appropriate format parameter:

where to_timestamp(field13, '<insert_correct_format_specifier>' < to_timestamp('2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

This of course means that your preformance will degrade if you were relying on an index on field13. Ideally, you should fix the column type of field13 to be a real date or timestamp.

Comments