DATETIME() and date formats -pretty confusing resu...

Erik_Ferm · 05-25-2021 07:54 AM

If your date format is dd/mm/yyyy which is common in Europe and not mm/dd/yyyy which I think is the standard in the US, the expression

DATETIME(TEXT(NOW()))

will not work.

TEXT(NOW()) return something like 25/05/2021 14:49:30.

DATETIME(25/05/2021 14:49:30) returns a blank as it expects the month to come first and there is no month 25.

So it appears that DATETIME() assumes that a date in text format follow the us convention regardless of local settings.

It gets even more interesting with EXTRACTDATETIMES().

ANY(EXTRACTDATETIMES(“13/5/2021”)) returns 13/05/2021 00:00:00

ANY(EXTRACTDATETIMES(“12/5/2021 x”)) return 05/12/2021 00:00:00
(the x is needed to make it a text input)

So ANY(EXTRACTDATETIMES(“13/5/2021 x”)) - ANY(EXTRACTDATETIMES(“12/5/2021 x”)) returns -4944:00:00

The number is negative because 5 December is 4944 hours after 13 May.

On the other hand

ANY(EXTRACTDATETIMES(“13.5.2021 x”)) - ANY(EXTRACTDATETIMES(“12.5.2021 x”)) returns 24:00:00. This is because 13 May is 24 hours after 12 May.

My guess is that the second example works as separating days and months with dots is a convention used in Germany and other places but not in the US. The problem with separating days and months with slashes is that it is done both in the US and in the UK but with the day and month in different orders.

So for a system based in the UK, the situation is that

DATETIME(TEXT(NOW())) will only work on dates where the month and the day are the same number
ANY(EXTRACTDATETIMES(TEXT(NOW()))) works from the 13th of each month and on the day where the date and day are the same number

I realise that different conventions for times and dates across the globe is a bit of a mess and that it is very hard to come up with a system that works everywhere.

Maybe it would make sense to consider adding an optional second parameter to some of the date and time functions where you can specify the date format?

Steve

Your mistake is wrapping NOW() with TEXT(). TEXT() explicitly converts a DateTime value to its display representation, whereas DATETIME() explicitly wants a Date, DateTime, or Time value in their native representation.

You’re using DATETIME() wrong.

See also:

Erik_Ferm

Steve,

I understand how the functions work.

My point is that DATETIME() assumes that a date entered as text is always in the US format and that EXTRACTDATES sometimes does.

In countries outside the US, this becomes an issue as I have tried to explain in my post.

Do you really think that

ANY(EXTRACTDATETIMES(“13/5/2021 x”)) - ANY(EXTRACTDATETIMES(“12/5/2021 x”))

should return a negative number?

Steve

No, it requires. The native date and time formats that AppSheet uses internally are US formats. All Date, DateTime, and Time values must be in US format when passed between functions. The display format can be localized, and the storage format can be localized, but the working format must be US.

Erik_Ferm

Steve,

It is clear that I am not expressing it very well but I understand how DATETIME() works and that the argument should be in US format.

My question was whether it would make sense to add an optional second parameter to specify another format.

As mentioned twice already, EXTRACTDATETIMES() is not consistent with DATETIME().

EXTRACTDATETIMES(“Text with 13/3/2021 and 12/3/2021”) returns a list with the items 13 March and 3 December. I don’t think this is what most users would expect.

To be consistent with DATETIME(), I would have to return only 3 December as “13/3/2021” is not a date in US format.

EXTRACTDATETIMES(“Text with 13-3-2021 and 12-3-2021”) gives the same result, so 13 March and 3 December.

EXTRACTDATETIMES(“Text with 13.3.2021 and 12.3.2021”) on the other hand returns 13 March and 12 March. This is also inconsistent with DATETIME() but at least the two dates in the text string are treated the same.

I think the way EXTRACTDATETIMES() treats dates makes is pretty much useless unless you are sure that all dates will be in mm/dd/yyyy format and I think a potential solution to this could be to add a parameter to specify the date format to look for in the string.

Are you happy with the way EXTRACTDATETIMES() is working at the moment?

Steve

I’m not happy with the way any of the EXTRACT() functions work. They are all US English-centric, and the technical implementations of all of them are severely flawed. But they are what they are, and my impression is that their flaws are considered a very, very low priority.

If you consider the current behavior a bug, I recommend you contact support@appsheet.com to at least get your complaint in the official record.

Swoopy

I got my work done because of your experiment and fine explanation. Thank you.

DATETIME() and date formats -pretty confusing results