Postgresql date format

I have a web application (written with python/django) that (due a bad specification) Web Forms expecting "YYYY-mm-dd" date format and others using "dd/mm/yy" date format.开发者_开发知识库

Is there a way to tell postgresql to accept dates in both formats? for example, to try "dd/mm/yy" and, if it fails, then try "yyyy-mm-dd".

That would be awesome.

---

From the fine manual:

Date and time input is accepted in almost any reasonable format, including ISO 8601, SQL-compatible, traditional POSTGRES, and others. For some formats, ordering of day, month, and year in date input is ambiguous and there is support for specifying the expected ordering of these fields. Set the DateStyle parameter to MDY to select month-day-year interpretation, DMY to select day-month-year interpretation, or YMD to select year-month-day interpretation.

PostgreSQL is more flexible in handling date/time input than the SQL standard requires. See Appendix B for the exact parsing rules of date/time input and for the recognized text fields including months, days of the week, and time zones.

So PostgreSQL should be able to deal with just about any date format you throw at it. Your "dd/mm/yy" format is, however, ambiguous. But, there is the DateStyle configuration parameter to help with such ambiguity.

For example:

=> create table x (d date not null);
=> insert into x values ('2001-01-10');
=> insert into x values ('Feb 2 2980');
=> insert into x values ('01/02/03');
=> select * from x;
     d      
------------
 2001-01-10
 2980-02-02
 2003-02-01

That said, I'd recommend moving everything to ISO 8601 (YYYY-MM-DD) internally and handle the conversions at the edges of the application. OTOH, there is reality to contend with so you should do whatever you have to do to make it go.

---

If those are the only two formats possible then it may be better to explicitly allow only those, rather than rely on postgres to interpret. For example:

with w as (select '2011-12-13' as input_date union select '13/12/2011')
select case when input_date~'^\d\d\d\d-\d\d-\d\d$' 
                 then to_date(input_date, 'yyyy-mm-dd')
            when input_date~'^\d\d/\d\d/\d\d\d\d$' 
                 then to_date(input_date, 'dd/mm/yyyy')
            end 
from w;

    case
------------
 2011-12-13
 2011-12-13
(2 rows)