Skip to main content

I have a date/time field, but somehow DAY_OF_WEEK(table.column) with %A as formatting always returns Thursday.

To strip the time, I'm using Round_day() which makes 2024-07-03 from a 2024-07-03 21:23:35 timestamp, but returns Thursday, but it should be Wednesday for July 3rd.

 

I can add a extra column from source tables, but prefer an easy PQL formula.

PS: I'm using the new view experience.

 

DAY_OF_WEEK ( ROUND_DAY( "eventproperties"."reportdate"))

weekday not working

Hi,

 

%a is working properly with date or timestamp. For example for date, or date with time it works good:

 

imageIt works bad because of day_of_week which outputs a int number 0-6. If you want to have proper weekday name based on a 0-6 int value use REMAP_VALUES+TO_STRING+DAY_OF_WEEK combo. I can assume it similar to setting formatting in Excel when date is int 1 (which is translated to 1s of January 1900), maybe because of Unix date system.

 

Code:

REMAP_VALUES(TO_STRING(DAY_OF_WEEK ("_CEL_AR_ACTIVITIES"."EVENTTIME")), [ '1' , 'Monday' ] , [ '2' , 'Something'] )

 

Example:

image 

Mark as best answer if that helped :)

 

Best Regards,

Mateusz Dudek

Hi Mateusz,

 

I added the weekday 'hardcoded' in my event property data. The simple %a or %A is not working, and the recode is not efficient.

Thanks for your answer, case closed for now.

Community Code of ConductAccessibility statement