How do I set the locale in the line below ?
Var foo As Variant = row.Column("BillDate").DateTimeValue
That was were I had trouble in the past days, and working on something else… with dates… let me understand the problem. But I do not found the solution in the documentation (yet).
Every DateTime object you create has either an implied or specified TimeZone. I believe (others will correct me) that storing a date in your database only stores the date-time, and when you extract it and assign it to a variable it assumes the local TimeZone, unless you override it when you assign it.
I have a data base with dates (just dates, month x 2, day x 2, year x 4), and as you can see readng my code I want to read to get a DateTime Object, but if the date stored in the db is not conform to MY locale, I get the above error. And I know that the dates are not conform.
The question is still here: how can I do that ?
Of course, I can guess (1) what field is the day / what field is the moth and rebuild the date, but in that case, I’d better create a good old paper data base and stop using a computer.
(1) Read all the dates and search where is the field who do not have a value > 12… this field is the Month field !
Then, I would have to check the months with 30 days, those with 31 days and at last February (my black beast).
For your “BillDate” do you take the time into account? or just the date?
What Database are you using? Is the database saving the locale in some form?
You saving to the database and then pulling from the database a different locale is used?
As DateTime has a constructor with seconds from 1970 and locale, people that need to know exactly the date/time and be able to show that in different locales save seconds from 1970 to the database as double instead of using DateTimeValue.
Edit: I see that you posted some information while I was finishing my post. Looks like you have access to different databases and you don’t know if the date will be 13-01-2024, 01-13-2024, or even other way like 01/13/2024 or 13.01.2024, is that the case?
Hi Alberto.
The only thing stored is the date in the MM-DD-YYYY format and my locale is DD-MM-YYYY.
No time, no fantazy. Works fine if I read using StringValue, but I want to have a DateTime so I do not have to care about 28, 29, 30, 31: only if this is a valid date.
Is it clear now ?
Xojo 2024r3.1/Sequoia on a M1
Yes, is clear now.
You need to use StringValue and then DateTime.FromString with the right locale.
OK, I give up and will do that.
API2… I have no word for it.
Hi Emile,
The solution can be pretty easy. I use the String value of the date from the database and convert it into a proper DateTime object.
These two lines call my EXTRACT_SQLDateTime_String() function, and then create a date in UTC:
Var SQLDateTime_String As String = EXTRACT_SQLDateTime_String(webhook_request_json.value("UpdatedOn").StringValue)
Var UpdatedOn_DateTime As DateTime = DateTime.FromString(SQLDateTime_String, Nil, New TimeZone("UTC"))
Here is my EXTRACT_SQLDateTime_String():
// Used to reduce the unique DateTime format returned by D_TOOLS API to a useable string for constructing a date object
' From API:
' e.g., "2024-12-03T19:58:02.0420118"
' Proper format to return:
' e.g., "2024-12-03 19:58:02"
Var new_date_str As String = date_from_API
new_date_str = new_date_str.ReplaceAll("T", " ")
Var pos_of_period As Integer = new_date_str.IndexOf(".")
new_date_str = new_date_str.Left(pos_of_period)
Return new_date_str
If you don’t want to use DateTime and use Date:
dim d as new date(s.Right(4).Val, s.Left(2).Val, s.Mid(4,2).Val)
If the date is stored as a string in the MM-DD-YYYY format, I might do something like the following, to convert the value to my locale.
Var tmp As String = row.Column("BillDate").StringValue
Var month As Integer = tmp.NthField("-", 1).ToInteger
Var day As Integer = tmp.NthField("-", 2).ToInteger
Var year As Integer = tmp.NthField("-", 3).ToInteger
// create a proper DateTime variable
Var dateValue As New DateTime(year, month, day)
// then display in your locale format
Var dateString As String = dateValue.ToString(Locale.Current, DateTime.FormatStyles.Short, DateTime.FormatStyles.None)
Dates should never be stored like this for that exact reason. There is an SQL standard format, YYYY-MM-DD. Dates should be stored in this format and then displayed in the locale’s format.
If I could 
this a million times I would.
In previous positions, I’ve had to a do a LOT of data clean up for data stores which did NOT follow standards. Dates were always one of the very (usually the most) difficult data types when trying to normalize data.
Standards exist because a LOT of smart people from a wide variety of backgrounds worked together to find the least-worst format for “99%” of all situations. Use them.
OK folks… start using metrics and °C and we will talk about ISO 8601.
This is not a debate. You have a data base with data and want to display them using the locale. Exporting the data base contents and modernizing it is a good idea, but whatever I use, I will display a date.
Of course, I use ISO 8601 here, there and everywhere ! 