Thread: Unpredictable text -> date implicit casting behaviour/to_date bug

The implicit casting of text to date is normally quite good, however i think it should only use one method during a
givenquery (or maybe transaction?) or maybe it should give a notice of some sort? - i was quite astonished when I
discoveredthe following: (look closely at row 4 and 5 vs the others)
 

SELECT ordredato, ordredato::date from old_faktura where status = 'ORD';ordredato |  ?column?  
-----------+------------18.10.01  | 2001-10-1831.10.01  | 2001-10-3101.11.01  | 2001-01-1103.11.01  |
2001-03-1103.11.01 | 2001-03-1110.11.01  | 2001-10-1126.11.01  | 2001-11-26
 


Added to the fact that to_date has a bug:

SELECT ordredato, ordredato::date, to_date(ordredato, 'DD.MM.YY') from old_faktura where status = 'ANN';ordredato |
?column? |    to_date    
 
-----------+------------+---------------18.04.00  | 2000-04-18 | 0001-04-18 BC30.08.01  | 2001-08-30 |
2001-08-3018.04.00 | 2000-04-18 | 0001-04-18 BC
 

It sometimes makes converting dates a little challenge...

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

"Aasmund Midttun Godal" <postgresql@envisity.com> writes:
> The implicit casting of text to date is normally quite good, however i
> think it should only use one method during a given query (or maybe
> transaction?)

Try setting DateStyle to match the date style you are using.  Postgres
is doing the best it can with ambiguous input.

> Added to the fact that to_date has a bug:

> SELECT ordredato, ordredato::date, to_date(ordredato, 'DD.MM.YY') from old_faktura where status = 'ANN';
>  ordredato |  ?column?  |    to_date    
> -----------+------------+---------------
>  18.04.00  | 2000-04-18 | 0001-04-18 BC
>  30.08.01  | 2001-08-30 | 2001-08-30
>  18.04.00  | 2000-04-18 | 0001-04-18 BC

I agree, this is not desired behavior.  The problem seems to be that the
logic in to_timestamp() tries to use "field is not zero" as a substitute
test for "field was provided".  At least in the case of the year fields,
this is *not* workable.  There must be a separate flag bit.
        regards, tom lane