How to write Date and Time string in the same format as it was read?

[Vodnik]

My application loads a CSV-file, modifies it and saves back. Some fields contain Date and Time recordings like:
30.12.2023 22:09:08
Format of Date and Time in CSV is defined by telephony system from which it is derived.
My OS is Windows, and Local Settings seems to match Date and Time format in the CSV file.
I use TryStrToDateTime(S,T) procedure to read the values and FormatDateTime('',T,[]) to write them back.
This works fine on my PC, but on user PCs with different Local Settings output looks like:
12/30/2023 22:09:08
Best solution in this case would be to write out date and time in the same format as it was read in.
Is it possible with standard routines? 
 

[tetrastes]

Code: Pascal  [Select][+][-]

1. FormatDateTime('dd.mm.yyyy hh:nn:ss', T)

[Remy Lebeau]

Best solution in this case would be to write out date and time in the same format as it was read in.

The best solution would be to use a locale-independent format, like ISO-8601, eg: 2023-12-30T22:09:08

Is it possible with standard routines?

Sure, you could use FormatDateTime(), if you know which format you want to use. That means you need to parse the input string to determine its formatting.  Standard routines won't help you with that.

[Vodnik]

Code: Pascal  [Select][+][-]

1. FormatDateTime('dd.mm.yyyy hh:nn:ss', T)

Yes, of course, but what if date and time format in file will be different (e.g. from another telephony system)?
Is it possible to "read" format from date time string?

[wp]

Is it possible to "read" format from date time string?

If you don't know anything about the format: No. Is "01/02/2024" the first of February 2024? Or the second of January? And even worse when years are shortened to two digits: "01/02/04"?

If the dates cover a longer range and if the year is in the 4-digit form there is some chance that you can separate days from months, though:
- Split the date string into its three parts, s1, s2, s3.
- The 4-digit part is the year - trivial...
- The part for which all values are <= 12 is the month
- The remaining part (in which all values are <= 31) then must be the day
- If both two-digit parts are <= 12 you're out of luck...

[Vodnik]

The best solution would be to use a locale-independent format, like ISO-8601, eg: 2023-12-30T22:09:08

File CSV is obtained from external system which is out of my control. I can't set ISO-8601 date format in it.
The same with my output file: it is supplied for further processing by customer. Format should be preserved.

Sure, you could use FormatDateTime(), if you know which format you want to use. That means you need to parse the input string to determine its formatting.  Standard routines won't help you with that.

I see, thanks.

[Zvoni]

If it's windows, what about GetLocaleFormatSettings ?

https://wiki.freepascal.org/Step-by-step_instructions_for_creating_multi-language_applications#Format_settings

[marcov]

Something strange about your story:  If your user's locale is different, it is not just the output that will fail, but the parsing of the incoming date will also be different.

[alpine]

Is it possible to "read" format from date time string?

If you don't know anything about the format: No. Is "01/02/2024" the first of February 2024? Or the second of January? And even worse when years are shortened to two digits: "01/02/04"?

If the dates cover a longer range and if the year is in the 4-digit form there is some chance that you can separate days from months, though:
- Split the date string into its three parts, s1, s2, s3.
- The 4-digit part is the year - trivial...
- The part for which the values are <= 12 is the month
- The part for which the values are <= 31 is the day
- If both two-digit parts are <= 12 you're out of luck...

Of course, It is a known fact that you can't deduce the format just from one date-time string.
Just a remote ideas:
Perhaps the CSV file covers some longer period - one can be lucky to find unambiguous date-time string and thus to discover the right format for the whole file.
Maybe the timestamps of the file (if not lost) have some relation to the file contents. They can be retrieved unambiguously and compared to the internal timestamps.
 

[TRon]

Of course, It is a known fact that you can't deduce the format just from one date-time string.

Unless in case that string is stored in a format that is unambiguous 

[alpine]

Of course, It is a known fact that you can't deduce the format just from one date-time string.

Unless in case that string is stored in a format that is unambiguous 

Is that the case?   

[TRon]

Is that the case?   

TS should make that the case, even if he mentions that he can't (their problem).

The issue TS is facing was created when someone wrote the software and decided it was a good idea to store the datetime string and make it dependent on the system's configured locale. It is fatal design decision and one that has to be dealt with properly or TS will have to deal with a lot of headaches.

That is why, before even writing a single line of code, you should think about your software design beforehand.

Your approach to solve one of those headaches is in theory a good one but depends on a variety of variables that can (still) make things go wrong. There is no 100% guaranteed solution to solve the problem, unless you are able to rely on data captured over a longer period of time (minimal 1 year) in order to determine the correct storage format. You can make all kind of assumptions in order to try and solve the issue but non of them is foolproof. So the question becomes how certain does TS want to be about the how the date time string is stored ?

[alpine]

Is that the case?   

TS should make that the case, even if he mentions that he can't (their problem).

The issue TS is facing was created when someone wrote the software and decided it was a good idea to store the datetime string and make it dependent on the system's configured locale. It is fatal design decision and one that has to be dealt with properly or TS will have to deal with a lot of headaches.

That is why, before even writing a single line of code, you should think about your software design beforehand.

Your approach to solve one of those headaches is in theory a good one but depends on a variety of variables that can (still) make things go wrong. There is no 100% guaranteed solution to solve the problem, unless you are able to rely on data captured over a longer period of time (minimal 1 year) in order to determine the correct storage format. You can make all kind of assumptions in order to try and solve the issue but non of them is foolproof. So the question becomes how certain does TS want to be about the how the date time string is stored ?

I know all that. But I can also sympathize the TS, since in my practice similar things happen on a daily basis. The other end may lack support, or the supporters may be just reluctant. Just trying to help   
BTW, the time span can be just over the 12-th of the month to have luck. Not needed to be a year long.

[TRon]

@alpine:
my apologies, it is/was not my intention to criticize your input on the subject other than to point out the obvious 'mistake'.

You and me might be aware but TS does not seem to be, despite wp's excellent explanation.

A simple test (and I kept the format strings relatively sane) shows that from 36 different datetime string formats the only thing you are able to determine is that it every individual part consist of the number/digits "01" (that test did not even include am/pm, day/month names (short or long) and or different separators).

The assumption with using 12 to determine between month and year says nothing about the year (or any other date/time part for that matter).

For all we know old(er) logs are analyzed, or the year is only displayed as 2 digits or that particular phone system is only up and running in the first half of the year or only handles/logs one single phone-call per month or per year (I have seen much stranger things than that in practice).

A deterministic approach can only succeed with enough data present that can be analyzed/tested against.

defined by telephony system from which it is derived.

and

Yes, of course, but what if date and time format in file will be different (e.g. from another telephony system)?

So TS should first determine how many different telephone systems there are (for use of his software), and if TS wishes to support them or not. Then determine how many different datetime formats should be dealt with/supported based on the logs of those phone systems.

You could try a deterministic approach for that with TryDateTimeToStr (though the documentation for TryDateTimeToStr is not very helpful and ambiguous imho).

TryStrToDateTime tries to convert the string S to a TDateTime date and time value, and stores the result in Value. The date must consist of 1 to three digits, separated by the DateSeparator character (1). If two numbers (!) are given, they are supposed to form the day and month of the current year. If only one number (!) is given, it is supposed to represent the day of the current month (This is not supported in Delphi). The time must consist of 1 to 4 digits, separated by the TimeSeparator character (2). If two numbers (!) are given, they are supposed to form the hour and minutes.

1/2: a bit strange use of the word digit, because in the date-string "21/12/2023" there are actually 8 digits and 3 numbers. No idea why there is supposedly a limit of 3 digits. Same for the time string, e.g. 11:22:33 (6 digits, 3 numbers, 2 time-separators).

But perhaps it is my lack of understanding the English language ?

The program/code of TS should not rely on any of the locale settings whatsoever and instead use the hard-coded format from the csv file (as determined by the phone system: datetime format string in = datetime format string out.


I've attached a small concept of using a deterministic approach using scandatetime (more so to avoid the use of formatsettings). Perhaps it is useful to someone.

[Zvoni]

Everything correct all of you said.

If those CSV's (might) come from different telephony systems, which use different DateTime-Representations, i'd try to go the easy way:
Precondition: The DateTime-Format on those telephony systems is independant of the locale of the Users attached to it
(e.g. i'm located in Germany, our Server is a "german" Server", i have german locale on my laptop, but we also employ people who have english locale, but who are "attached" to our "german" Server) and this DateTime-Format is consistent on that particular telephony server

Meaning: Everytime a "new" Client decides to use his Software, TS should inquire about the DateTime-Format used in those CSV's

Then I'd keep a "mapping" somewhere (In a Database, a simple textfile, in my Sockdrawer, wherever).
Example:
1) OP's first client is german, and sends his CSV containing german DateTime-Format --> keep a record, that for CSV's from this client the Format is "dd.mm.yyyy hh:mm:ss"
2) OP's second client is american, and sends his CSV containing american DateTimeformat --> new record that for CSV's from this client the format is "mm/dd/yyyy hh:mm:ss"
and so on

then it's a simple lookup, and OP would have the information, in which format to write back his results (whatever those are)