switch function not working with 'dayOfWeek'?

yfzh

Hi all,

I tried to apply a condition to find the latest previous weekday date, so for example, if today is Sunday or Monday, then it will Friday, for other days it will just be yesterday. I tried using switch function like this:

switch(

datePart(now(), 'dayofweek'),

7, trunc(inc(asDate(now(), 'yyyy-MM-dd'), -2, 'day'),'days'),

1, trunc(inc(asDate(now(), 'yyyy-MM-dd'), -3, 'day'),'days'),

trunc(inc(asDate(now(), 'yyyy-MM-dd'), -1, 'day'), 'days')

)

It doesn't work and always give me the default output. (I tried to test datePart(now(), 'dayofweek') == 1 and it returns True. However, when I instead use "weekDay", it gave me correct answer.

switch(

datePart(now(), 'weekday'),

"Sunday", trunc(inc(asDate(now(), 'yyyy-MM-dd'), -2, 'day'), 'days'),

"Monday", trunc(inc(asDate(now(), 'yyyy-MM-dd'), -3, 'day'), 'days'),

trunc(inc(asDate(now(), 'yyyy-MM-dd'), -1, 'day'), 'days')

)

Since 'weeday' is not available in SQL, I'd like to use the dayofweek logic to be able to use SQL engine. I'm wondering if there is a bug in the switch function or I just didn't set the true condition for dayOfWeek to be correct?

Thanks in advance!

Operating system used: Windows 11

Turribeach

You should be careful since Dataiku's formula language runs on Java and will set the first day of the week based on your DSS machine locale setting whereas when the recipe executes in SQL it will use the database's locale to determine the first day of the week (ie either Sunday or Monday). These two can easily be different depending on how your DSS and database locale are set. If they differ it will even be more confusing for users as the Prepare recipe preview will be done using the Java logic but when the recipe runs it will use SQL. Therefore I suggest you stay away from using Dataiku formula language for this calculation and you do it in a SQL recipe to always get a consistent result. Then use the value on visual recipes normally.

yfzh

https://community.dataiku.com/discussion/comment/44882#Comment_44882

Thanks for the reminder! Yeah, later I changed to use SQL instead which works fine for me now.