Timestamp and Char comparision

[kumariaru]

Hi,

I need to compare value1= '2010-06-03 22:29:42:109615'(this in character) and to a timestamp field value2='16-JUL-10 04.42.44.296985000 PM' in a SQL.

Table value format is: 16-JUL-10 04.42.44.296985000 PM'
Value compared format is:'2010-08-03 22:29:42:109615'

I need to display values where value2 > value1.

I do not want to convert value2 to char.I tired converting value1 to timestamp.
Expression : TO_TIMESTAMP('2010-08-03 22:29:42:109615', 'DD-MON-RR HH.MI.SSXFF PM')

But this is throughing an error :

SQL Error: ORA-01843: not a valid month
01843. 00000 - "not a valid month".

Please advice if I am missing anything in expression.

Thanks in advance...

kumari

 

[carp]

Try using 'MM' instead of 'MON'. 'MON' expects values such as 'JAN','FEB', etc. You are feeding it '08', which does not match your format spec.

 

[harris79]

You say that the table value is a particular format, is it a varchar or is it a date field?

If it is the latter try a TO_DATE -

TO_DATE('2010-08-03 22:29:42:109615', 'YYYY-MM-DD HH24:MI:SSxFF')

Steve

Steve