DatePart invalid in UK

[paul_durrant]

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.

[Charlie Arehart]

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. 

[paul_durrant]

plus ...

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

Both return "Tuesday" - which is correct.

[BKBK]

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

[paul_durrant]

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 · Accepted solution]

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);

[paul_durrant]

Thanks both of you - Charlie for the local option and BKBK for the clarity with the sample DatePart syntax.

Paul