Thread: to_date function

Carlos Henrique Reimer <carlos.reimer@opendb.com.br> wrote:

> I've a Linux box running postgresql 8.2.17 and facing some strange results
> from the to_date function.

> As you can see in the following tests the problem occurs when the template
> used includes upper and lower case characters for the minute (Mi or mI).

> Am I using the incorrect syntax or is it a bug?
> [...]

In general, the template patterns are case-sensitive (cf.
"month" vs. "Month" vs. "MONTH"). So "mI" will probably be
interpreted as a literal "m" and "I" meaning "last digit of
ISO year" which isn't what you want.

  So use "MI" and be happy.

Tim

Carlos Henrique Reimer <carlos.reimer@opendb.com.br> writes:
> I've a Linux box running postgresql 8.2.17 and facing some strange results
> from the to_date function.

> As you can see in the following tests the problem occurs when the template
> used includes upper and lower case characters for the minute (Mi or mI).

> Am I using the incorrect syntax or is it a bug?

The defined template patterns are MI and mi.  Random combinations of case
aren't accepted.  I believe what's actually happening is that the code
is seeing this as separate letters m (which matches nothing so it's not
a template pattern but just constant text) and i or I, which are the
ISO-week patterns.  More recent versions of PG throw an error

ERROR:  invalid combination of date conventions
HINT:  Do not mix Gregorian and ISO week date conventions in a formatting template.

but 8.2 probably just does something not too sensible with trying to
combine the ISO and Gregorian field values :-(

            regards, tom lane

Hi,
on a Windows/PostgrSQL 8.4 three from your queries throw errors (sorry,
but they are in german):

select to_date('01/04/2013 23:59:59','DD/MM/YYYY HH24:Mi:SS');
=> FEHLER:  ungültige Kombination von Datumskonventionen
HINT:  Die Gregorianische und die ISO-Konvention für Wochendaten können
nicht einer Formatvorlage gemischt werden.

select to_date('01/04/2013 23:59:59','DD/MM/YYYY HH24:mi:SS');
=> 2013-04-01

select to_date('01/04/2013 23:59:59','DD/MM/YYYY HH24:mI:SS');
=> same error as above

select to_date('01/04/2013 23:59:59','DD/MM/YYYY hH24:MI:SS');
=> FEHLER:  ungültiger Wert »:5« für »MI«
DETAIL:  Der Wert muss eine ganze Zahl sein.

Regards
Ludwig

Am 07.07.2010 23:07, schrieb Carlos Henrique Reimer:
> Hi
> I've a Linux box running postgresql 8.2.17 and facing some strange
> results from the to_date function.
> As you can see in the following tests the problem occurs when the
> template used includes upper and lower case characters for the minute
> (Mi or mI).
> Am I using the incorrect syntax or is it a bug?
> Thank you in advance!
> template1=# select to_date('01/04/2013 23:59:59','DD/MM/YYYY
> HH24:Mi:SS')       ;
>   to_date
> ------------
>  2009-04-01
> (1 row)
> template1=# select to_date('01/04/2013 23:59:59','DD/MM/YYYY HH24:mi:SS')
> ;
>   to_date
> ------------
>  2013-04-01
> (1 row)
> template1=# select to_date('01/04/2013 23:59:59','DD/MM/YYYY HH24:mI:SS')
> ;
>   to_date
> ------------
>  2009-04-01
> (1 row)
> template1=# select to_date('01/04/2013 23:59:59','DD/MM/YYYY
> hH24:MI:SS')       ;
>   to_date
> ------------
>  2013-04-01
> (1 row)
>
> --
> Reimer
>