Overriding the system Date Format

Question

In my application I am reading from one database and writing to a second. The app is quick and dirty so I am reading / writing using AsString on both the FieldByName and ParamByName of the queries.

This works for all my use cases apart from where the data type is Date or DateTime

As far as I can tell FieldByName.AsString uses the system ShortDateTime format to return dates as (in my case) dd/mm/yyyy. The database expects the date to be written in as yyyy-mm-dd

According to Delphi Basics I should be able to set ShortDateFormat to what I need, but it appears that in XE5 this is no longer the case (correct?)

Further digging on here returns these two questions that use TFormatSettings to override the local settings. However, both of these use the resulting FormatSettings in StrToDate and FormatDateTime directly.

So two questions

1) Can I tell my application to override the System ShortDateFormat?

2) If so, How (I have a Plan B if not)?

Answer 1

The use of a global variable for the date and time formats was a mistake committed long ago by the original RTL designers. Functions that rely on the global format settings, like the single parameter StrToDate are retained for backwards compatibility, but you should not be using them.

For conversions between date/time and string you should:

1. Initialise a TFormatSettings instance with your date format.
2. Call the two parameter StrToDate, passing your TFormatSettings to convert from a string to a date.
3. Call FormatDateTime overload that accepts a TFormatSettings when converting in the other direction.

Now, to the main thrust of your question. You should not be using strings at all for your dates and times in the scenario you describe. Use AsDateTime rather than AsString. If you happen to have a database column that does store a date/time as a string, then you'll should use the TFormatSettings based conversion functions to work around that design fault.

If you are absolutely dead set on doing this all with strings, and I cannot persuade you otherwise, then you need to use FormatSettings.ShortDateFormat from SysUtils to control your short date formatting.