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)?

You should use date time type columns in your database and use `AsDateTime` casting to fill the values. Storing date time as string is bad. — TLama, Oct 31 '13 at 15:23
The Fields in the DB are set to `DateTime`. MySQL always seems to choke (at least under DBX) on writing to the columns using `AsDateTime` so I generally use `FormatDateTime` to write `AsString`. In *this case* keeping as string may be preferable. — Dan Kelly, Oct 31 '13 at 15:32
Sounds to me like you ought to work out why you cannot use `AsDateTime`. — David Heffernan, Oct 31 '13 at 15:34

score 6 · Accepted Answer · answered Oct 31 '13 at 15:25

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:

Initialise a TFormatSettings instance with your date format.
Call the two parameter StrToDate, passing your TFormatSettings to convert from a string to a date.
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.

Thanks David. Very clear. As commented above the DB is set to `DateTime` where appropriate, we've just never managed to persuade MySQL/DBX to accept `AsDateTime` on writes. The example I am working on is rather dynamic with its columns so adding column type recognition will be a pain, but possible. The extra info on TFormatSettings is however useful going forward. — Dan Kelly, Oct 31 '13 at 15:37

Overriding the system Date Format

1 Answers1