• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

DatePart invalid in UK

Explorer ,
Apr 22, 2024 Apr 22, 2024

Copy link to clipboard

Copied

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.

Views

155

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Expert , Apr 23, 2024 Apr 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 dat

...

Votes

Translate

Translate
Community Expert ,
Apr 22, 2024 Apr 22, 2024

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 23, 2024 Apr 23, 2024

Copy link to clipboard

Copied

plus ...

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

 

Both return "Tuesday" - which is correct.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 23, 2024 Apr 23, 2024

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 23, 2024 Apr 23, 2024

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 23, 2024 Apr 23, 2024

Copy link to clipboard

Copied

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

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 23, 2024 Apr 23, 2024

Copy link to clipboard

Copied

LATEST

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

 

Paul

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation