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

Question

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

Is it anything to do with the year being 4 digit but specified as YY? — Charleh, Jun 15 '16 at 19:20
http://stackoverflow.com/questions/10319935/timestamp-conversion-in-oracle-for-yyyy-mm-dd-hhmmss-format Check the second answer. And @Charleh's comment is basically your answer. — Adam, Jun 15 '16 at 19:21
Is `field3` an actual `timestamp` data type? Or is it potentially a string that is supposed to represent a date or a timestamp? — Justin Cave, Jun 15 '16 at 19:37
Try `to_timestamp( '2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )` or even `< add_months (systimestamp, -36)` — Wernfried Domscheit, Jun 15 '16 at 19:38
You also quietly changed the date from `12` to `15`. This suggests that you may be **retyping** code here rather than **copying and pasting** code here. Please only copy and paste. Otherwise, little differences creep in. Like the aforementioned `12` -> `15` — Bob Kaufman, Jun 15 '16 at 19:38
As a debug step, can you verify that `select to_timestamp( '2013-06-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ) from dual` returns the expected result? If that works, the problem must be in your data. — Hans Keﬆing, Jun 15 '16 at 19:46
@HansKesting Your code worked so the problem does not lie with the data. — Allie Hart, Jun 15 '16 at 19:54
@AllieHart - yes, the problem does lie in the data, meaning the data **in the table** (ae_dt509 ). Can you answer the comment by Justin Cave? — Hans Keﬆing, Jun 15 '16 at 19:57
Then apparently (at least) one of those strings is invalid as a date. — Hans Keﬆing, Jun 15 '16 at 20:03

score 1 · Accepted Answer · answered Jun 15 '16 at 19:54

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.

Thanks, This will help until I fix the column – Allie Hart Jun 15 '16 at 20:07 — Allie Hart, Jun 15 '16 at 20:07

score 0 · Answer 2 · answered Jun 15 '16 at 23:55

0

The select count () from ae_dt509 where field13 < to_timestamp( 2013-06-15 00:00:00, 'YYYY-MM-DD HH24:MI:SS' )* is throwing Bind Variable "00" is NOT DECLARED because the date is not enclosed in '' quotes

answered Jun 15 '16 at 23:55

Arcan3

89
1
5

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

2 Answers2