Skip to main content
P
paul_durrant
Inspiring
April 22, 2024
Answered

DatePart invalid in UK

  • April 22, 2024
  • 2 replies
  • 719 views

Day of week is displayed incorrectly, as if the date is in US format mm/dd/yyy.  How can I fix this?
(UK format is dd/mm/yyyy)

eg
Datepart for 01/05/2002 returns value of 7 for Saturday (not 4 for Wednesday).
UK : 01/05/2002 = 1 May 2002 = Wednesday.
US : 01/05/2002 = 5 Jan 2002 = Saturday.

 

More puzzling is that Datepart for 21/04/2022 returns 5 (for Thursday), when it should return 1 for Sunday, and in a US format a month of "22" is not valid anyway.


From my test output :
01/05/2002 Wed is output as datepart & day of week : 7 day of week Saturday
21/04/2022 Sun is output as datepart & day of week : 5 Thursday
22/04/2022 Mon is output as datepart & day of week : 6 Friday
23/04/2022 Tue is output as datepart & day of week : 7 Saturday
24/04/2022 Wed is output as datepart & day of week : 1 Sunday
25/04/2022 Thu is output as datepart & day of week : 2 Monday
26/04/2022 Fri is output as datepart & day of week : 3 Tuesday
27/04/2022 Sat is output as datepart & day of week : 4 Wednesday


My investigation so far :

The fault lies with the command "datepart" (ColdFusion not SQL), when used with command "DayOfWeekAsString".
Window Servers that run ColdFusion are set for UK in Control Panel settings.

Test logic of "Locale #GetLocale()#" displays "Locale English (UK)"

In JVM parameters the language is "-Duser.language=en" (should this be set to en_GB?)

I can workaround it by setting up day of week number in SQL and passing that to CF logic, but this is just avoiding the problem.

    This topic has been closed for replies.
    Correct answer BKBK

    Hi @paul_durrant ,

     

    Thanks for the update. 

    Charlie has given you the cause of the issue - locale timezone - as well as a possible solution. I only wish to share another point of view.

     

    Your code misses some accuracy . CFML is weakly-typed and so it allows all sorts. For accuracy, the second argument of 

     DatePart("w", "01/05/2002");

    should actually be a date object, not a string as in your code. In addition, you can increase the accuracy further by specifying the timezone or locale of the date. 

     

    For example, something like this:

    DatePart("w", lsParseDatetime("01/05/2002","English(UK)"));

    or

    DatePart("w", lsparsedatetime("01/05/2002","en_gb"));

    The following dump will give you the full list of locales available in ColdFusion

    writedump(server.coldfusion.supportedLocales);

     

     

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    April 23, 2024

    Please share a sample of the code that is causing the issue.

    P
    paul_durrantAuthor
    Inspiring
    April 23, 2024

    From the test page (as the main one uses SQL database reads which would be harder for you to test).

    Output : 

    01/05/2002 Wed is output as datepart & day of week : 7 day of week Saturday
    21/04/2022 Sun is output as datepart & day of week : 5 Thursday
    22/04/2022 Mon is output as datepart & day of week : 6 Friday
    23/04/2022 Tue is output as datepart & day of week : 7 Saturday
    24/04/2022 Wed is output as datepart & day of week : 1 Sunday
    25/04/2022 Thu is output as datepart & day of week : 2 Monday
    26/04/2022 Fri is output as datepart & day of week : 3 Tuesday
    27/04/2022 Sat is output as datepart & day of week : 4 Wednesday
     
    Code : 
    <cfscript>
DatePart0= DatePart("w", "01/05/2002");
DatePart1= DatePart("w", "21/04/2022");
DatePart2= DatePart("w", "22/04/2022");
DatePart3= DatePart("w", "23/04/2022");
DatePart4= DatePart("w", "24/04/2022");
DatePart5= DatePart("w", "25/04/2022");
DatePart6= DatePart("w", "26/04/2022");
DatePart7= DatePart("w", "27/04/2022");

DW0 = DayOfWeekAsString(DatePart("w", "01/05/2002"));
DW1 = DayOfWeekAsString(DatePart("w", "21/04/2022"));
DW2 = DayOfWeekAsString(DatePart("w", "22/04/2022"));
DW3 = DayOfWeekAsString(DatePart("w", "23/04/2022"));
DW4 = DayOfWeekAsString(DatePart("w", "24/04/2022"));
DW5 = DayOfWeekAsString(DatePart("w", "25/04/2022"));
DW6 = DayOfWeekAsString(DatePart("w", "26/04/2022"));
DW7 = DayOfWeekAsString(DatePart("w", "27/04/2022"));

writeOutput("01/05/2002 " & "Wed    is output as datepart & day of week : " & DatePart0 & " day of week " & DW0 & "<br/><br/>" );
writeOutput("21/04/2022 " & "Sun    is output as datepart & day of week : " & DatePart1 & " " & DW1 & "<br/>" );
writeOutput("22/04/2022 " & "Mon    is output as datepart & day of week : " & DatePart2 & " " & DW2 & "<br/>" );
writeOutput("23/04/2022 " & "Tue    is output as datepart & day of week : " & DatePart3 & " " & DW3 & "<br/>" );
writeOutput("24/04/2022 " & "Wed    is output as datepart & day of week : " & DatePart4 & " " & DW4 & "<br/>" );
writeOutput("25/04/2022 " & "Thu    is output as datepart & day of week : " & DatePart5 & " " & DW5 & "<br/>" );
writeOutput("26/04/2022 " & "Fri    is output as datepart & day of week : " & DatePart6 & " " & DW6 & "<br/>" );
writeOutput("27/04/2022 " & "Sat    is output as datepart & day of week : " & DatePart7 & " " & DW7 & "<br/>" );
</cfscript>
    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    April 23, 2024

    Hi @paul_durrant ,

     

    Thanks for the update. 

    Charlie has given you the cause of the issue - locale timezone - as well as a possible solution. I only wish to share another point of view.

     

    Your code misses some accuracy . CFML is weakly-typed and so it allows all sorts. For accuracy, the second argument of 

     DatePart("w", "01/05/2002");

    should actually be a date object, not a string as in your code. In addition, you can increase the accuracy further by specifying the timezone or locale of the date. 

     

    For example, something like this:

    DatePart("w", lsParseDatetime("01/05/2002","English(UK)"));

    or

    DatePart("w", lsparsedatetime("01/05/2002","en_gb"));

    The following dump will give you the full list of locales available in ColdFusion

    writedump(server.coldfusion.supportedLocales);

     

     

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    April 22, 2024

    Paul, let's start with something to get us all on the same page.

     

    First, if you output the CFML now() function, does it reflect your current time?  And what do you get when you dump the gettimezoneinfo() function? Finally, though you should not need to rely on it, have you tried adding a value to the optional second locale arg of the dayofweekasstring() function? Let us know what you see with these:

     

    <cfscript>
writeoutput(now() & "<br>");
writedump(gettimezoneinfo());
writeoutput(dayofweekasstring(1,"English (UK)") & "<br>");
</cfscript>

     

    And note that beyond affecting the locale with a JVM arg, you can also affect the timezone with one--otherwise it picks up that of the machine on which CF is running. The arg f and value for London, for example, would be:

    -Duser.timezone=Europe/London

    And such a JVM arg can be added to cf (if done carefully) via  the cf admin jvm page or cf's jvm.config file. More on setting such args is covered in various resources, or ask any questions. 

    /Charlie (troubleshooter, carehart. org)
    P
    paul_durrantAuthor
    Inspiring
    April 23, 2024

    plus ...

    writeoutput(dayofweekasstring(DatePart("w", Now()), "English (UK)") & "<br>");
    writeoutput(dayofweekasstring(DatePart("w", Now())) & "<br>");

     

    Both return "Tuesday" - which is correct.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices