What is the literal string needed to update date column in Oracle?

[Argo]

I tried 'YYYYMMDD-HH:MM:SS', 'MM-DD-YYYY HH:MM:SS' and every single combination I could think of. I keep getting all sorts of errors from "This is not a valid month to invalid literal string.

I know for timestamps it's 'YYYYMMDD-HH:MM:SS:mmm' but can't figure out what it is for dates.

 

[Drakkon]

um use the conversion function:
http://techonthenet.com/oracle/functions/to_date.php

 

[Mitzi]

The standard Oracle format for dates on all installations I've seen is DD-MON-YYYY (i.e. 23-AUG-2005) though it depends on your installation. The easiest thing to do would be to stick a to_date function round whatever you are trying to update to force a specific date mask i.e. update my_table set date_column = to_date('23-AUG-2005', 'DD-MON-YYYY')

 

[Velk]

Mitzi is correct in that out of the box it's '23/Aug/2005', but your DBA can determine the default date format in the database preferences.

The command

alter session set nls_date_format='DD/MM/YYYY'

Will change the format for your current session, thus avoiding having to wrap every date in to_date - this is useful if you are doing ad-hoc SQL. For scripts you probably want to use to_date.