Welcome to the SRP Forum! Please refer to the SRP Forum FAQ post if you have any questions regarding how the forum works.
Dates in 2023
Is anyone else having issues with Select statements and dates in the year 2023? Specifically Jan 10/11?
We have various screens that do future scheduling. We use a calendar control which gives us an OI date.
The code has always used this OI date which eventually gets used in a RList sentence like this:
Sentence = "SELECT ORDER_BOOK WITH DISP_QTY > 0 AND WITH DUE_DATE_1 >= ":Quote(DateFrom)
That seems to workup to about Jan 10th, 2023.
Any OI date AFTER that doesn't work until it is gets reformatted first:
DateFrom = Fmt(DateFrom,"[HRM1_DATE,4]")
Regardless of which format the date should be stored and used in, anyone have any idea what this magical date is that has a different requirement?
We have various screens that do future scheduling. We use a calendar control which gives us an OI date.
The code has always used this OI date which eventually gets used in a RList sentence like this:
Sentence = "SELECT ORDER_BOOK WITH DISP_QTY > 0 AND WITH DUE_DATE_1 >= ":Quote(DateFrom)
That seems to workup to about Jan 10th, 2023.
Any OI date AFTER that doesn't work until it is gets reformatted first:
DateFrom = Fmt(DateFrom,"[HRM1_DATE,4]")
Regardless of which format the date should be stored and used in, anyone have any idea what this magical date is that has a different requirement?
Comments
20100. {nice round number that. conincidence ...?}
With the 'fix': 11/01/2023
I always under the impression, (for whatever reason), that selects of any kind should use the Iconv value for dates.
Turns out, that was incorrect.
Whilst it may work, internally OI assumes the date is oconved/formatted. If that works, (whether it is the dates you expected or not) then it returns those keys. If it doesn't then it will assume their Iconved and try again.
Maybe not exactly like that but something like that.
So the iconved value of 20100 is actually interpreted as an oconved value, maybe 02/01/00 or something.
Regardless of the actual value, OI reads it as formatted and performs the select.
Glad to know we aren't the only ones !
Your explanation makes sense and lines up with what we are seeing. I guess internally 20100 must be the start of the Goldilocks of numbers whereby it doesnt try IConv as a fallback...
All because it was totalling about sixty shifts from everywhere but within that week.
That totally makes sense then as to why there is no return!!!
I already knew the 'workaround' (i.e. the right way) but its good to have some reasoning behind the why.
I had this problem too.
In my case, OpenInsight will return wrong results if the btree search string is the date in the year 2023.
Anyone have any idea how I solve this problem
Thank You
Albert
Try and format the date first so you are not passing in a OI date.
I like using the SRP_DATE "FORMAT" service for this (I find it more intuitive than fmt/oconv).
It is conceivable that both BTree.Extract and RList process the dates in the same way that Kevin noted...
The invoice date record is in a OI date, so I have to search the data using a OI date. The BTree.Extract worked well before, but since 2023, I can't search the data correctly if the search date is the date in the year 2023.
I have changed my code below but OI still return wrong results
START_DATE = IConv("15/12/2022", "[SRP_DATE]")
END_DATE = IConv("04/01/2023", "[SRP_DATE]")
SEARCH_STRING = "INVOICE_DATE" : @VM : START_DATE - 1 : "~" : END_DATE + 1 : @FM
Regards
Albert
What @Opto_Will is suggesting is that you Oconv the dates before using them in your SEARCH_STRING. Your dictionary items should also be properly formatted so the system will now how to properly Iconv your dates.
Incorrect data being returned. Jumped in and thought, beauty, I got this and changed the select to use Oconved dates. Didn't fix it.
Turned out, the dictionaries just had the default "D" formatting whilst we use "DE". Had never been a problem before now.
It takes me back to the turn of the century fears about the millenium bug.
Just limited to OI applications only.
It is interesting because Btree indexes do store the internal value. Thus, it is natural to think that we should just pass in internal values in Btree.Extract. However, for reasons that have been documented in the Revelation KnowledgeBase, the format of the dictionary column will be used. Thus so if you format your dates differently from the U.S. default, you need to make sure the dictionary references the same format so it can Iconv it properly.
If your system is using internal date formats in R/List Select statements, or in Btree.Extract calls, you may be encountering issues and getting back incorrect results.
The internal date formats, as of January 2, 2023 ( which is 20100 internally), are also valid external dates, and if you have not Oconv’d your dates in your search string or selection criteria, (externally formatted, for example "01 Jan 2023" or "01/01/23") you will get back incorrect results.
This was noted in Knowledge Base article number 42, over 30 years ago:
https://revelation.com/revweb/KB_Articles/KB0042.htm
And, an updated blog posting can be found here:
https://sprezzblog.blogspot.com/2023/01/y2k-redux-indexing-issues-with-dates.html
Hopefully your systems are all using the external versions of dates in your searches. If not, make those changes as soon as possible.
"DE", "D2/E", "D/E", "D4/E" for example, are all the same but if you just had "D" on the dict whilst using formats like we down here, then you would have a problem.
The method that R/List and Btree.Extract use to confirm if the value being passed is a valid date is to use the Input Validation Pattern of the dictionary rather than the Output Format. In most systems this will either be "D" (Month Day Year) or "DE" (Day Month Year).
This is important because if the system is going to validate the date, it most know whether the month is first or the day is first because "01/02/2023" is ambiguous. What is it, January 2 2023 or February 1 2023? Similarly, is "01/30/2023" a valid date? Yes in the US but no in Europe and in Oz. Only the Input Validation Pattern can settle these questions properly.
Interestingly enough, even those edge case internal date values that validate successfully will validate differently based on the Input Validation Pattern. Let's take the infamous trigger date that kicked off this firestorm: 20100.
We know that 20100 is January 11, 2023. We know that OI will successfully Iconv 20100 because it is possible to read this as formatted date. But what date does it think it is? Let's take a look:
Iconv(20100, 'D') = 11720 = February 1, 2000
Iconv(20100, 'DE') = 11690 = January 2, 2000
Therefore, properly validated dictionaries (especially dates, but it can apply to other data types with internal values that differ from output patterns) are a must.
Well explained @DonBakke!
You are correct!
And Double Whammy points for you for joining both Yoda and Don's approved slogan!!
Iconv( '20099', '[SRP_DATE]') = 15239 = September 20, 2009
Iconv( '20100', '[SRP_DATE]') = 11982 = October 20, 2000
Iconv( '20101', '[SRP_DATE]') = 12347 = October 20, 2001
Of course, it shouldn't be a problem if used properly ;).