Subscribe to this thread
Home - General / All posts - Interpreting DATETIME strings in SQL9 - caveats
tjhb

8,410 post(s)
#16-Jun-18 23:00

I am not sure whether this is as expected. In any case it currently requires care.

My system locale is set to English (New Zealand), which uses short date format d/MM/yyyy, the same as English (Australia), and almost the same format as English (United Kingdom), French (France), Spanish (Spain) and others, which all use dd/MM/yyyy. German (Germany) and Russian (Russia) both use dd.MM.yyyy. These selected examples use the same day-month order, with small variations.

By contrast, English (United States) uses M/d/yyyy, month first.

This is well known.

But look what happens in SQL9. Again, my locale is set to use d/MM/yyyy.

--SQL9

? DateTimeDay(CAST('6/4/1920 0:00:00' AS DATETIME))

--> 4

? DateTimeDay(CAST('6/04/1920 0:00:00' AS DATETIME))

--> 4

? DateTimeDay(CAST('4/6/1920 0:00:00' AS DATETIME))

--> 6

? DateTimeDay(CAST('12/31/1920 0:00:00' AS DATETIME))

--> 31

? DateTimeDay(CAST('31/12/1920 0:00:00' AS DATETIME))

--> 31

In other words, date-time strings are being interpreted as if they were expressed in US order, not local order--except where that is not possible (the last example).

(Results are the same if we leave off the time part of each string.)

This looks like a serious trap, with errors that will not always be obvious.

LeRepère122 post(s)
#17-Jun-18 14:53

I also noticed that when I use the format "yyyy-mm-dd" the date registration works well in a datetime format field of an M9 array.But, does not work well with "dd-mm-yyyy";the dd becomes the month and mm the day when the dd is less than 13. It's even worse with the format "dd-mm-yy".

lionel

519 post(s)
online
#18-Jun-18 02:45

Strange but Microsoft OS when search for date let us change the format and how date and list are display !!

I ll try to see if change this ( after reboot or not ) ll change how manifold manage date content !!

AS you can see i use FR keyboard Azerty but OS language Interface ( Help menu ..) is in english !!!

Attachments:
OS_format_rawORrendering.png


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

adamw


8,259 post(s)
#21-Jun-18 16:26

All CASTs are en-US on purpose, they have to behave similarly regardless of the system the query runs on. This affects more than just dates: floating-point values (always '1.234' instead of '1,234' on de-DE systems), booleans (always 'true' / 'false'), etc.

We will add a function to convert a date to a string and back with the language (collation) passed explicitly, with an option to use the current system setting.

This is similar to the text functions like StringToUpperCase / StringToUpperCaseCollate.

tgazzard
136 post(s)
#11-Nov-18 22:20

Hi Guys,

This is problematic. For example in 9.0.168.4 you can copy a table out of postgres and paste it into the m9 environment and it will change the order of day and month for all values where it possible. It doesn't give you any warning that it is doing this....

So if the day is 10 and the month is 9 (10/9) Then it will reverse this. So the month is 10 and the day is 9 (9/10).

But it keeps the day and month in the correct order where it doesn't make sense to change it.

For example 29/10 is the the 29/10.

It doesn't do this in m8.

From my perspective, the expected behaviour is that it keeps the day/month order.

Copy and paste were performed using the buttons in the side panel.

adamw


8,259 post(s)
#12-Nov-18 10:12

We'll check this. Thanks for the report.

adamw


8,259 post(s)
#12-Nov-18 12:13

A follow-up:

We found a possible reason for the issue (9 may misinterpret date values read from PostgreSQL if database date style is set to something other than ISO).

However, we are puzzled as to how this could have been not affecting 8. Are you positive 8 can read date values from the exact same database that 9 misreads them? If so, it would be helpful to know the version of PostgreSQL, the locale on the machine, and the output of 'SHOW DATESTYLE' command on the database.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.