Our Python CMS stores some date values in a generic "attribute" table's varchar column. Some of these dates are later moved into a table with an actual date column. If the CMS user entered an invalid date, it doesn't get caught until the migration, when the query fails with an "Invalid string date" error.
How can I use Python to make sure that all dates put into our CMS are valid Oracle string date representations?
How can I use Python to make sure that all dates put into our CMS are valid Oracle string date representations?
I'd change the approach a bit. Have Python parse the original date input as forgivingly as possible, then output the date in a known-good representation.
dateutil's liberal parser may be a good place to start:
import dateutil.parser
d= dateutil.parser.parse('1/2/2003')
d.strftime('%d-%b-%y')
I'm not sure '%d-%b-%y' is actually still the right date format for Oracle, but it'll probably be something similar, ideally with four-digit years and no reliance on month names. (Trap: %b is locale-dependent so may return unwanted month names on a non-English OS.) Perhaps “strftime('%Y-%m-%d')” followed by “TO_DATE(..., 'YYYY-MM-DD')” at the Oracle end is needed?
The format of a date string that Oracle recognizes as a date is a configurable property of the database and as such it's considered bad form to rely on implicit conversions of strings to dates.
Typically Oracle dates format to 'DD-MON-YYYY' but you can't always rely on it being set that way.
Personally I would have the CMS write to this "attribute" table in a standard format like 'YYYY-MM-DD', and then whichever job moves that to a DATE column can explicitly cast the value with to_date( value, 'YYYY-MM-DD' ) and you won't have any problems.
Validate as early as possible. Why don't you store dates as dates in your Python CMS?
It is difficult to know what date a string like '03-04-2008' is. Is it 3 april 2008 or 4 march 2008? An American will say 4 march 2008 but a Dutch person will say 3 april 2008.