update on date field, invalid month ora1843

[blom0344]

I am trying to do an update on a time and a date field. Both are defined as DATE.

UPDATE XXXXX
SET MSG_TIME_ACK = '01-01-00 9:18:16' , MSG_DATE_ACK = '02-01-02'
WHERE A = 'CON' AND
B IS NULL

This gives an invalid month error, solved by using JAN instead of 01.

However:

UPDATE XXXXX
SET MSG_TIME_ACK = '01-JAN-00 9:18:16' , MSG_DATE_ACK = '02-JAN-02'
WHERE A = 'CON' AND
B IS NULL

results in the stalemate situation that the length of the string is to long:

ORA-01830: date format picture ends before converting entire input string

Does anyone have a clue how to solve this mess??

Thanks in advance............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[smoriano]

Hi,

If both fields are date, you have "to say" to database wich format are you using in the string like this:

UPDATE XXXXX
SET MSG_TIME_ACK = to_date('01-00-00 9:18:16','DD-MM-YY HH:MI:SS') , MSG_DATE_ACK = to_date( '02-00-02','DD-MM-YY')
WHERE A = 'CON' AND
B IS NULL

must work right, to enter the date as JAN:

UPDATE XXXXX
SET MSG_TIME_ACK = to_date('01-JAN-00 9:18:16','DD-MON-YY HH:MI:SS') , MSG_DATE_ACK = to_date( '02-JAN-02','DD-MON-YY')
WHERE A = 'CON' AND
B IS NULL

 

[blom0344]

Hello smoriano,

worked quite nicely , thank you ............. T. Blom
Information analyst
tbl@shimano-eu.com