Updating a date in Oracle SQL table

Question

I am trying to update a date in a SQL table. I am using Peoplesoft Oracle. When I run this query:

Select ASOFDATE from PASOFDATE; 

I get 4/16/2012

I tried running this query

UPDATE PASOFDATE SET ASOFDATE = '11/21/2012'; 

but it is not working.

Does anyone know how I would change the date to the one desired?

Answer 1

This is based on the assumption that you're getting an error about the date format, such as an invalid month value or non-numeric character when numeric expected.

Dates stored in the database do not have formats. When you query the date your client is formatting the date for display, as 4/16/2011. Normally the same date format is used for selecting and updating dates, but in this case they appear to be different - so your client is apparently doing something more complicated that SQL*Plus, for example.

When you try to update it it's using a default date format model. Because of how it's displayed you're assuming that is MM/DD/YYYY, but it seems not to be. You could find out what it is, but it's better not to rely on the default or any implicit format models at all.

Whether that is the problem or not, you should always specify the date model:

UPDATE PASOFDATE SET ASOFDATE = TO_DATE('11/21/2012', 'MM/DD/YYYY'); 

Since you aren't specifying a time component - all Oracle DATE columns include a time, even if it's midnight - you could also use a date literal:

UPDATE PASOFDATE SET ASOFDATE = DATE '2012-11-21'; 

You should maybe check that the current value doesn't include a time, though the column name suggests it doesn't.