Skip to main content
New Participant
August 25, 2022
Question

Getting invalid month in Dateformat

  • August 25, 2022
  • 2 replies
  • 626 views

I have a website that is running the foloowing script.

<CFQUERY NAME="CreateFile" DATASOURCE="#xxxxxxx#"> INSERT INTO tblxxxxx

(LicenseID, CompanyID, LocationID, Company_Status, Company_Type, SYS_EntryDateTime)

VALUES

('#LicenseID#', '#CompanyID#', '#LocationID#', '#Company_Status#', '#Company_Type#',
'#DateFormat(Now(), "MM/DD/YYYY")# #TimeFormat(Now(), "h:mm tt")#')

</CFQUERY>

and if I look at the query run on mssql, here is what I get

('xxxxxxxxxxxxxxxxxxxx', 'xxxxxxxxxxxxxxxxxxxxx', 'xxxxxxxxxxxxxxxxxxxx', '3', '6', '08/237/2022 12:05 PM')

The month value should have been 25, not 237 which is invalid.

Any thoughts would be appreciated.

 

    This topic has been closed for replies.

    2 replies

    BKBK
    Braniac
    September 1, 2022

    Add the following flag to the JVM properties (for example, by editing /bin/jvm.config):

     

     -Dcoldfusion.datemask.useDasdayofmonth=true

     

    Then restart the ColdFusion instance.

     

    New Participant
    October 4, 2022

    does it matter where in the JVM.config file -Dcoldfusion.datemask.useDasdayofmonth=true is placed ?

    BKBK
    Braniac
    October 5, 2022

    @miker34395621 , it does not matter. As long as it is one of the settings in java.args.

    Save the modified jvm.config file, then restart ColdFusion.

    Participating Frequently
    August 26, 2022

    Wrong day mask

     

    See documenation - DateFormat (adobe.com) 

    Charlie Arehart
    Braniac
    October 7, 2022

    To be clear, it's not so much a "wrong" day mask. Instead, the problem is that until CF2021 came out, Adobe always regarded a d or D dateformat mask as being "day of month". But in CF2021, they changed it (totally unexpectedly, and without warning) to follow the Java pattern where D means something VERY different: day of YEAR!

     

    And so lots of apps started breaking, or creating incredibly incorrect results--again perhaps without any notice or warning. Just devastating. I did a blog post on it then, if anyone may want to read more on all this.

     

    And within weeks of CF2021's release (in Nov 2020), Adobe came out with a "fix", which entailed enabling the JVM arg and value that BKBK mentions below. I did a blog post on that then, also. At the time, the fix ALSO required implementing a new hotfix jar file, but that requirement was lifted with update 1, as it incorporated the fix that made that jvm arg work.

     

    But to be clear, that update 1 (or any beyond it, up to 4 currently) does NOT change the requirement that if you want to have CF treat a "D" dateformat just like a "d", you MUST add that JVM arg, set to true

     

    Finally, there's yet another way to "solve" this problem. In that second blog post I point to, I highlight and point to editor regex strings which commenters had kindly shared, so that if you instead wanted to find and CHANGE your code to switch uppercase D dateformat masks to use a lowercase d where that is what's meant. See the next to last section of that second post.

     

    As onerous as all this is, it does not seem Adobe will do any more. It seems they will require us to accomodate this change, rather than reverting the default behavior to pre-2021 days. Hope that's helpful.

    /Charlie (troubleshooter, carehart. org)