Return weekday from a stored int value with sql

[former_SQLA_member1694869]

CREATE TABLE WeekDay (DeliveryWeekdays INTEGER NOT NULL);

INSERT INTO WeekDay(DeliveryWeekdays) values (7);

We have a program that stores an int value in a table with which weekdays you have selected in a check box.

{
    None = 0,
    Monday = 1,
    Tuesday = 2,
    Wednesday = 4,
    Thursday = 8,
    Friday = 16,
    Saturday = 32,
    Sunday = 64,
}

Ex: If you have checked Monday and Thursday the value is 9 in the field in the table. If you checked Monday, Tuesday, Wednesday the value is 7 in the field and so on..

The question is whether with a date I can check if the day of the week in that date is in the int value stored in the table.

If I check the date 2020-10-08, I see that it is a Thursday. I want to check if Thursday is in value 7. The answer is yes. If the int value is 3, the answer will be no.

[VolkerBarth]

I'm not sure I understand your requirements (and is the second/third last statements true?) - but when you want to compare a value against set bits in an integer, SQL Anywhere offers bitwise operators here, see Bitwise operators. So to check against the "Thursday" flag, you could test with <myvalue> & 0x0008 != 0, and for "Friday" with <myvalue> & 0x0010 != 0.

[former_SQLA_member1694869]

You are right, the answer in both my examples is false. I wrote wrong there. Ok, this is probably what I'm looking for. How do I write this in a sql in a condition?

[VolkerBarth]

It's difficult to show some code if we don't know how you access the test date (the possible delivery date, I assume) and the according "possible delivery week days" (per customer, I further assume). So it would be helpful to show some sample table entries...

[former_SQLA_member1694869]

Yes, that's right. I thought of overall examples of how I can use this in practice. How, based on a date, can I convert Friday (5) to 0x0010 etc.

[VolkerBarth · Accepted solution]

Here's a sample based on Frank's approach (corrected for Sunday) - in real use cases, you would provide the test date and the "allowed weekday flags" via table data, I assume:

-- use with clause to calculate the number of days for the current month
-- via subtracting one day from the next month's first day
-- Note: This is just used to build a list of all days of the current month,
-- so just for testing purposes
with cd as (select current date as cur_date,
            day(dateadd(dd, -1, ymd(year(cur_date), month(cur_date) + 1, 1))) as current_month_day_count)

-- list every day of this month and its weekday value and turn that into an according flag
-- and compare this with a given "allowed_days_flag" (here for Monday, Tuesday and Friday)
select ymd(year(cd.cur_date), month(cd.cur_date), current_month.row_num) as test_date,
   dow(test_date) weekday,
   dayname(test_date) dayname,
   power(2, if weekday = 1 then 6 else weekday - 2 end if) dayofweek_flag,
   0x01 | 0x02 | 0x10 as allowed_days_flags,
   if dayofweek_flag & allowed_days_flags = dayofweek_flag then 1 else 0 endif as fitting
from cd cross apply dbo.sa_rowgenerator(1, cd.current_month_day_count) current_month
order by 1;

This will return the folowing list:

test_date   weekday dayname dayofweek_flag  allowed_days_flags  fitting
2020-10-01  5   Thursday    8   0x13    0
2020-10-02  6   Friday  16  0x13    1
2020-10-03  7   Saturday    32  0x13    0
2020-10-04  1   Sunday  64  0x13    0
2020-10-05  2   Monday  1   0x13    1
2020-10-06  3   Tuesday 2   0x13    1
2020-10-07  4   Wednesday   4   0x13    0
2020-10-08  5   Thursday    8   0x13    0
2020-10-09  6   Friday  16  0x13    1
2020-10-10  7   Saturday    32  0x13    0
2020-10-11  1   Sunday  64  0x13    0
2020-10-12  2   Monday  1   0x13    1
2020-10-13  3   Tuesday 2   0x13    1
2020-10-14  4   Wednesday   4   0x13    0
2020-10-15  5   Thursday    8   0x13    0
2020-10-16  6   Friday  16  0x13    1
2020-10-17  7   Saturday    32  0x13    0
2020-10-18  1   Sunday  64  0x13    0
2020-10-19  2   Monday  1   0x13    1
2020-10-20  3   Tuesday 2   0x13    1
2020-10-21  4   Wednesday   4   0x13    0
2020-10-22  5   Thursday    8   0x13    0
2020-10-23  6   Friday  16  0x13    1
2020-10-24  7   Saturday    32  0x13    0
2020-10-25  1   Sunday  64  0x13    0
2020-10-26  2   Monday  1   0x13    1
2020-10-27  3   Tuesday 2   0x13    1
2020-10-28  4   Wednesday   4   0x13    0
2020-10-29  5   Thursday    8   0x13    0
2020-10-30  6   Friday  16  0x13    1
2020-10-31  7   Saturday    32  0x13    0

[former_SQLA_member1694869 · Accepted solution]

One question, how do I get the dow to work if Monday is the first day of the week?

[VolkerBarth · Accepted solution]

It should not matter here because DOW is not dependent on the "first_day_of_week" option, to quote:

The DOW function is not affected by the value specified for the first_day_of_week database option. For example, even if first_day_of_week is set to Monday, the DOW function returns a 2 for Monday.

If you want to additionally "list" the week day according to your current locale (and you have set the "first_day_of_week" option to 1 or whatever), you can still add the according value via "select ..., datepart(dw, test_date), ..." to the query above, as dateapart(dw) does reflect that option. But the calculation itself is easier via DOW because, as stated, it's not dependent on that option.

[former_SQLA_member1694869 · Accepted solution]

Ok, thanks.

A quick question. Your example is very good but I'm probably too stupid to understand it how works.

If I retrieve value 7 from a field in my select, how can I use the Power function to check if the day of the week in datepart (cdw, today ()) is in 7? I know that value 7 in this case contains Monday = 1, Tuesday = 2, and Wednesday = 4 so it should return 1. If I run this select tomorrow, my value will be 0.

[VolkerBarth · Accepted solution]

Well, as stated originally:

It's difficult to show some code if we don't know how you access the test date (the possible delivery date, I assume) and the according "possible delivery week days" (per customer, I further assume). So it would be helpful to show some sample table entries...

You have decided not to do so, so I guess I can't give further help .

[fvestjens]

Is this what you're looking for:

select dow(current date) weekday, POWER(2,weekday-2) theday, (if (theday & 0x12) = 0 then 1 else 0 endif)