PostgreSQL date formatting error

[markus]

Hi,

I've encountered problem with date fields when i changed datestyle variable of my DB.

usually i work with datestyle=ISO, YMD, but now i needed
datestyle=SQL ,MDY

with such settings postgresql returns date fields like this: 1/14/2013

With such result i get 'Invalid argument to date encode'.

When i looked under the hood i found out that in ZSysUtils.pas
function TimestampStrToDateTime(String)
arbitrary decodes date in order Year, Month, Date.
With my text of '1/14/2013' i get Year=1, Month=14 and Day=2013 and this causes error.

Maybe this function should check ShortDateFormat to see what is order of date elements?

Best regards,
Marek

[EgonHugeist]

markus,

that's an issue i was waiting for, Marek. Well the current way of Zeos allways adapts to the local settings. But there are some mor DBRMs like Oracle or MSSQL which can change the Data/Time-Format with the current session.

Another example: http://www.lazarus.freepascal.org/index ... 521.0.html

etc.

I was thinking several times about it. Your ShortDateFormat won't match all cases. Example: 11.11.11. What is year, Month, and day?

The datestyle you've changed: Is it session dependend?

IMHO a new Property like DateTimeFormat for our TZConnection or TZDataSet Components would be the best option. Any other, better proposals?

[markus]

Michael,

You are right ShortDateFormat would be good if DB date formatting would be just like system locale settings, but it might be completely different
Your example is actually vey simple - it doesn't matter which field is year month or day;)

But property of connection is a good idea - it will be formated just as developer needs:)

Regards,
Marek

[EgonHugeist]

markus,

Your example is actually vey simple - it doesn't matter which field is year month or day;)

YES it was an example of a nerd!

But it shows clearly what i mean.

Well we've Zeos7.1-alpha and we can implement such a FormatSetting. But currently i'm running over all web-sites and bug-trackers to close issue reports or support some people with Zeos.

So it can take a while...

[markus]

ok, thank you Michael.

In that case i make myself quick patch based on my first suggestion: ShortDateFormat, which will work good for me.

And when You find time to patch this properly i'll gladly download it:)

Best regards,
Marek

[EgonHugeist]

markus,

i made a patch on 7.2. Simply adde Dateformat='mm-dd-yyy' to the Connection.Properties. Timeformat and DatetimeFormat is supported too now.