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

User · Answer

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

User · Answer

Just to add to Alex Poole s answer  here is how you do the date and time        TO DATE  31 DEC 2017 12 59 59    dd mm yyyy hh24 mi ss

User · Answer

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

User · Answer

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

[sql] Updating a date in Oracle SQL table

Examples related to sql

Examples related to oracle

Examples related to date

Examples related to peoplesoft