Updating a date in Oracle SQL table

Updating a date in Oracle SQL table

This is based on the assumption that youre 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 its using a default date format model. Because of how its displayed youre assuming that is MM/DD/YYYY, but it seems not to be. You could find out what it is, but its 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 arent specifying a time component – all Oracle DATE columns include a time, even if its midnight – you could also use a date literal:

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

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

Here is how you set the date and time:

update user set expiry_date=TO_DATE(31/DEC/2017 12:59:59, dd/mm/yyyy hh24:mi:ss) where id=123;

Updating a date in Oracle SQL table

If this SQL is being used in any peoplesoft specific code (Application Engine, SQLEXEC, SQLfetch, etc..) you could use %Datein metaSQL. Peopletools automatically converts the date to a format which would be accepted by the database platform the application is running on.

In case this SQL is being used to perform a backend update from a query analyzer (like SQLDeveloper, SQLTools), the date format that is being used is wrong. Oracle expects the date format to be DD-MMM-YYYY, where MMM could be JAN, FEB, MAR, etc..

Leave a Reply

Your email address will not be published.