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?

Comments

  • What is the actual vale in DateFrom, before the select?
  • let me take out the fix code and drop a debug to double check....
  • Definately OI.
    20100. {nice round number that. conincidence ...?}

    With the 'fix': 11/01/2023
  • Had this problem a couple of times this year and again yesterday.
    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.
  • Ok , never ever use internal date as a search value - always date oconv it.
  • @AusMarkB

    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...
  • Just confirmed that when using the "D" conversion, 20100 is a valid date in external format: Feb 1, 2000.
  • @Opto_Will, yes just yesterday a client was trying to schedule an employee for some leave in February and was being warned that employee already had over 500 hours scheduled for that week.
    All because it was totalling about sixty shifts from everywhere but within that week.
  • Thanks @KevinFournier and @AusMarkB

    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.



  • Hi,

    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
  • @albertkurniawan
    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...
  • Hi @Opto_Will


    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
  • @albertkurniawan

    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.
  • @DonBakke again has come to the party with the extra sneaky ingredient. As well as always using Oconved dates, the dictionaries must match the conversion to be used. I hit this nugget just yesterday.
    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.
  • We also ran into this recently, but I think it was also the result of OI 9.4.6 being less forgiving when using internal values in Btree.Extract.

    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.
  • For those that didnt get the Revlation blast that included the original KB issue as well as a related recent Sprezzatura blog post ...

    We’re sending this email to alert you to a possible issue in your OpenInsight or Arev system.

    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.


  • @ausmarkb yes, the fact that the rlist oconv must match the dictionary oconv is very concerning.
  • edited January 2023
  • @josh, fortunately it doesn't need to be identical, just enough to distinguish between US and European formatting, ie is the first number the day or the month.
    "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 oconv is just a display thing. the fact that it affects data processing is concerning.
  • Some of what I have read suggests to me that there might still be a little bit of confusion. It seems as if some think the Output Format is the important issue and is referenced under the hood. This is not the case so I want to make sure there is proper understanding with this issue.

    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.
  • I've already dropped the Yoda so I can't do it again but.......

    Well explained @DonBakke!
  • @Opto_Will, a Yoda suitable for all occasions

  • @AusMarkB.
    You are correct!

    And Double Whammy points for you for joining both Yoda and Don's approved slogan!!
  • Probably noteworthy that the default [SRP_DATE] conversion has its own interpretation of unformatted dates, different to OI.
    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 ;).
Sign In or Register to comment.