Expand my Community achievements bar.

SOLVED

Derive Day of Week from Date

Avatar

Level 2

I used the Day function on a date field and it returns an integer. How do we convert this to the day of the week - Mon, Tue etc.?

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi,

 

In an xtk expression? You'd have to use a long chain of decode's or iif's. Assuming Day() counts from 1:

decode(Day(@date), 1, 'Sun', decode(Day(@date), 2, 'Mon', decode(Day(@date), 3, 'Tue', ...))))

 

Thanks,

-Jon

View solution in original post

4 Replies

Avatar

Correct answer by
Community Advisor

Hi,

 

In an xtk expression? You'd have to use a long chain of decode's or iif's. Assuming Day() counts from 1:

decode(Day(@date), 1, 'Sun', decode(Day(@date), 2, 'Mon', decode(Day(@date), 3, 'Tue', ...))))

 

Thanks,

-Jon

Avatar

Level 2

Thanks. I was going down the same path - I was going to use Case statements. Was wondering if there was any pre-built function. Will try both, thanks!

Avatar

Level 2
Update. I used the Weekday function to derive the day of week from date. FYI - week starts at Day 0. So 0 = Monday and so on. Then converted this to String. Then used iif to say if 0 then Monday, else if 1 Tuesday and so on. Thanks.