Sage 100

 View Only

  • 1.  Dates to Numbers

    Posted 07-31-2025 15:58

    Somewhere between Version 2021 and 2024, Crystal Reports started having a problem with converting dates to numbers.  In Version 2024, creating a formula ToNumber(date field) generates a message "The string is non-numeric".  In Version 2021 there is no such message and the calculations are correct.

    Thinking it might be the commas or something else, I created a string formula for the month, another for the day, and another for the year, strung them together in one beautiful string (displays perfectly, 01012024 ex.), and created a formula ToNumber(beautiful string).  Same message.

    Has anyone encountered this and have any tips to overcome?



    ------------------------------
    Susan Pawlowic
    Sage 100 Solutions Architect
    RKL eSolutions, LLC
    spawlowic@rklesolutions.com
    (717) 735-9109 Ext. 4352
    ------------------------------


  • 2.  RE: Dates to Numbers

    Posted 07-31-2025 16:04

    I only use tonumber when I need to do math on a string value (and usually prefix with an isnumeric check to prevent report crashes).

    For a formatted date, I use ToText, where you can specify the format you want.

    ToText(currentdate,"yyyyMMdd")



    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------



  • 3.  RE: Dates to Numbers

    Posted 07-31-2025 21:17

    Has anyone encountered this and have any tips to overcome?

    ChatGPT 



    ------------------------------
    Wayne Schulz
    wayne@s-consult.com
    Schulz Consulting
    (860) 516-8990
    CT
    ------------------------------



  • 4.  RE: Dates to Numbers

    Posted 08-01-2025 11:23

    @Susan Pawlowic, you didn't state your purpose of converting a date to a number but if it is to determine the number of days between the two dates, use DateDiff instead.

    DateDiff("d", {StartDate}, {EndDate})

    where "d" can be any of these:

    • "yyyy" - Years
    • "q" - Quarters
    • "m" - Months
    • "w" - Weeks
    • "d" - Days
    • "h" - Hours
    • "n" - Minutes
    • "s" - Seconds

    Apologies if I missed your point.

    FWIW - Answer from claude.ai. My goto AI for coding.



    ------------------------------
    Greg Stiles
    DSD Business Systems
    ------------------------------

    Original Message


  • 5.  RE: Dates to Numbers

    Posted 08-01-2025 14:00

    As @Greg Stiles mentioned, I'm not sure what the reason is for getting the date in numeric format.  Another lengthy method to get a date in a numeric format is to convert it to Julian.  The link below from SAP details how to convert a Julian number to a date and how to convert a date to Julian format.

    https://userapps.support.sap.com/sap/support/knowledge/en/1263435

    Interestingly ProvideX has a JUL function that makes the conversions easy.



    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Chauffeur, Chef, and Personal Assistant to Sprinkles
    ------------------------------

    Original Message


  • 6.  RE: Dates to Numbers

    Posted 08-01-2025 14:55

    The custom reports are being recreated in v2024 from v2021 and Work Order tables are being swapped out for Production Management tables.  There are many formulas, with advanced language beyond my skillset (and the author is long gone), and they interact with each other in an unusual way.  Since I don't know what the formulas are saying to begin with, much less how they're interfacing with one another, I'm not able to determine if the date statement can be said differently.  I did create a new report and made the date numeric, which worked.  I think it has something to do with the bigger formula within which this statement is buried.  In summary, I am in over my head and will be getting a programmer involved!



    ------------------------------
    Susan Pawlowic
    Sage 100 Solutions Architect
    RKL eSolutions, LLC
    spawlowic@rklesolutions.com
    (717) 735-9109 Ext. 4352
    ------------------------------

    Original Message


Related Content