This discussion is archived
7 Replies Latest reply: Jan 11, 2013 8:00 AM by malky RSS

having issues casting timestamp to date

malky Explorer
Currently Being Moderated
Hi there,

So here's what I have.. I have a timestamp in my database and i need to return it as a date (because what used to work in WL8.1 now is giving me a java.lang.IllegalArgumentException: Cannot convert this to java.sql.Date in WL10). So i'm changing my query as follows:

I do run my old query to get a sample:
select col_key, col_time, col_other
from table1 where col_key = 'a'

... and i get for col_time *'01/11/2013 1:18:04.354261 PM'* ... so far so good.

So i grab that value and I do the following to test my format:

select CAST(to_timestamp('01/11/2013 1:18:04.354261 PM', 'MM/DD/YYYY HH:MI:SS.ff PM') AS DATE) from dual;
works perfect, returns the date: 01/11/2013 1:18:04 PM


Given the above I apply that to my old query now:
select col_key, CAST(to_timestamp(col_time, 'MM/DD/YYYY hh:mi:ss.ff PM') AS DATE), col_other
from table1 where col_key = 'a'

and I get a "ORA-01858: a non-numeric character was found where a numeric was expected"

... where did I go wrong?

Thanks
  • 1. Re: having issues casting timestamp to date
    sb92075 Guru
    Currently Being Moderated
    post results of following SQL

    SELECT SYSDATE FROM DUAL;
  • 2. Re: having issues casting timestamp to date
    malky Explorer
    Currently Being Moderated
    01/11/2013 10:47:55 AM
  • 3. Re: having issues casting timestamp to date
    JohnWatson Guru
    Currently Being Moderated
    Is col_time a timestamp column? If so, you can't pass it as an argument to to_timestamp, it is one already. This works,
    orcl> select cast(systimestamp as date) from dual;
    
    CAST(SYSTIMESTAMP
    -----------------
    11-01-13 15:45:38
    but this can't,
    orcl> select cast(to_timestamp(systimestamp, 'MM/DD/YYYY hh:mi:ss.ff PM') as date) from dual;
    select cast(to_timestamp(systimestamp, 'MM/DD/YYYY hh:mi:ss.ff PM') as date) from dual
                             *
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected
  • 4. Re: having issues casting timestamp to date
    sb92075 Guru
    Currently Being Moderated
    malky wrote:
    01/11/2013 10:47:55 AM
    notice that DATE datatype does not contain any fractions of a second.

    It is a bad practice to use Reserved Words, like DATE, as column name or alias.
    NOTHING you did explicitly converted TIMESTAMP into DATE datatype.

    In order to convert a string into a DATE use TO_DATE() function
  • 5. Re: having issues casting timestamp to date
    malky Explorer
    Currently Being Moderated
    Yes it is a timestamp column...

    So ok, that's how I read online I could convert a timestamp to date.. if that's not the case... then how do i return a timestamp column as a date?
    I was trying to_date but there's no formatting for milliseconds hence why some people would suggests using to_timestamp and then casting... and if that doesn't work then what can I do? WL10 doesn't convert timestamps to java.sql.Date, so I need from the query to give it a Date instead of a timestamp.
  • 6. Re: having issues casting timestamp to date
    JohnWatson Guru
    Currently Being Moderated
    malky wrote:
    Yes it is a timestamp column...

    So ok, that's how I read online I could convert a timestamp to date..
    Well, what you are doing is trying to convert a string into a timestamp, and then convert the timestamp to a date. Which works, if you pass a string.
    if that's not the case... then how do i return a timestamp column as a date?
    I've already shown you in my sample code!
    I was trying to_date but there's no formatting for milliseconds hence why everyone suggests using to_timestamp... and if that doesn't work then what can I do? WL10 doesn't convert timestamps to java.sql.Date, so I need from the query to give it a Date instead of a timestamp.
  • 7. Re: having issues casting timestamp to date
    malky Explorer
    Currently Being Moderated
    Oh my apologies!!

    I thought:
    select cast(systimestamp as date) from dual;

    was just like printing a sysdate or something.. it worked great thanks a lot!!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points