Find non-numeric character instead of a numeric character

Question

I work with oracle 11 G

and I want to update my table

but I have a problem in format of date

the type of my column is timeStamp and this is an example of date in my column : 11/12/05 16:08:00,000000000

this is my java code

String query = "UPDATE transfers SET date_closed_transfer = '" +
                new Date() + "', date_arch = '" +
                new Date() + "', date_archhj = '" +
                dateArchHj + "',action_type = '2',status_transfer = '" + status +
                "' WHERE id_transfer = '" + id + "'";

this is my query :

 UPDATE transfers

    SET date_closed_transfer = to_date('Mon Mar 24 10:39:25 GMT 2014','dd/mm/yy')',

    date_arch = to_date("Mon Mar 24 10:39:25 GMT 2014","dd/mm/yy")', 

    date_archhj = '1435/5/23',
    action_type = '2',
    status_transfer = 'AP'
    WHERE id_transfer = 'TR-70'

but I have this error :

Caused by: java.sql.SQLException: ORA-01858: Caractère non numérique trouvé à la place d'un caractère numérique

Updated :

I try with this query :

UPDATE transfers SET date_arch = to_timestamp_tz('Tue Mar 25 09:29:14 GMT 2014','DY Mon DD HH24:MI:SS TZD YYYY'), date_archhj = '1435/5/24',action_type = '2',status_transfer = 'A' WHERE id_transfer = 'TR-78'

but I have this error :

Erreur SQL : ORA-01846: ce n'est pas un jour de semaine valide 01846. 00000 - "not a valid day of the week"

also I try with :

UPDATE transfers SET date_closed_transfer = '25/03/2014', date_arch = '2014-03-25 09:59:47.123', date_archhj = '1435/5/24',action_type = '2',status_transfer = 'A' WHERE id_transfer = 'TR-61'

but also I have an error :

Erreur SQL : ORA-01843: ce n'est pas un mois valide 01843. 00000 - "not a valid month"

this is a valid example of date which should e in my column

31/03/02 00:00:00,000000000

Can you use bind variables instead of concatenating literals? — Jon Heller
– Jon Heller, Commented Mar 25, 2014 at 2:20

Community · Accepted Answer · 2017-05-23 12:11:11Z

1

The format mask that you provide in the TO_DATE function needs to match the string. Plus, you can use the TO_TIMESTAMP function, because your column is of TIMESTAMP datatype. Therefore, it should be as below:

Updated query:

to_timestamp_tz('Mon Mar 24 10:39:25 GMT 2014','DY Mon DD HH24:MI:SS TZR YYYY')

References:

2 Comments

franco Over a year ago

thank you for your answer but I still have an error :

Joseph B Over a year ago

I checked the query again. TZR (Time Zone Region) works for your timestamp string. docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm

Collectives™ on Stack Overflow

Find non-numeric character instead of a numeric character

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related