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

8,227 post(s)
online
#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ère121 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

457 post(s)
#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,090 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.

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