DateTimeFormat %U for 1/1/2023 returns 01 instead of 00

[njkollauf]

1/1/2023 is the start of 2023 and it falls on a Sunday.

When you calculate DateTimeFormat([date],'%U') where [date] = 1/1/2023, the result is 01, instead of 00

The documentation for DateTimeFormat %U reads: This returns the week number, as 00 – 53, with the beginning of weeks as Sunday.

1/1/2023 is not following this logic, why not?

[ShankerV]

Hi @njkollauf 

Got your issue, the same has occurred in the past on 01/01/2017.

The below post will help to solve your issue.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Calculating-Week-Number-using-DateTime...

Many thanks

Shanker V

[IraWatt]

Weird problem, great find @ShankerV. Strange logic

[njkollauf]

Hey Shanker,

This does not resolve my issue.

I was able to adjust the formula to have Sunday being the start date and that returns the below for my test dates:

 

Noting that isoWeekNumber and isoWeekSingleFormula don't even match...

The formula gets closer. The starting point for isoWeekNumber should be 00, I add +1 to the "Desired Result" to get an integer value that I use elsewhere.

Nick

[ShankerV]

Hi @njkollauf 

Sorry. I didnt get your question exactly.

[njkollauf]

 Shanker, it is not that you didn't answer my question exactly. It is that you didn't test the results in the solution provided.

The solution you provided may have worked in 2017, but it is not working for the 1/1/2023 date as it places it either in week 53 or week 1 when it should place it in week 00.

[PanPP · Accepted solution]

Hi @njkollauf 

1) There appear to be some dates where it does not match the ISO standard..

2) Please note that ISO can differ from the actual year as well.. 

%U	This will return the week number, as 00 – 53, with the beginning of weeks as Sunday.
%W	This will return the week number, as 00 – 53, with the beginning of weeks as Monday.

To note.. DateTimeFormat([Date],"%W") or using %U does not return the correct ISO week number

Workflow attached is from this post.. with inputting in 2023-01-01 date..

To answer your question.. 2023-01-01 is considered week 52..

Week number  From Date To Date

Week 52, 2022	December 26, 2022	January 1, 2023
Week 01	January 2, 2023	January 8, 2023

This post (page 2) by JohnJPS provides more insights on ISO dates..

Hope this is able to answer your question. If it does, please like the post and mark it as a solution.

Cheers.

[ShankerV]

Hi @njkollauf 

Please be informed that, I do have tested the same and I found the results were matching as per the ISO format 8601.

PadLeft(ToString(FLOOR(DateTimeDiff(DateTimeAdd([dt],4-Switch(DateTimeFormat([dt],"%a"),7,"Mon",1,"Tue",2,"Wed",3,"Thu",4,"Fri",5,"Sat",6),"days"),DateTimeParse(ToString(ToNumber(DateTimeFormat(DateTimeAdd([dt],4-Switch(DateTimeFormat([dt],"%a"),7,"Mon",1,"Tue",2,"Wed",3,"Thu",4,"Fri",5,"Sat",6),"days"),"%Y")))+'-01-01',"%Y-%m-%d"),"days")/7)+1),2,"0")

https://myweb.ecu.edu/mccartyr/isowdcal.html

The output generated as 52 is as per the ISO format.

Hope this helps!!!!