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. |
|
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 One question, how do I get the dow to work if Monday is the first day of the week?
(11 Oct '20, 16:07)
Rolle
Replies hidden
It should not matter here because DOW is not dependent on the "first_day_of_week" option, to quote:
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.
(12 Oct '20, 04:45)
Volker Barth
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.
(14 Oct '20, 14:12)
Rolle
Replies hidden
Well, as stated originally:
You have decided not to do so, so I guess I can't give further help .
(15 Oct '20, 07:38)
Volker Barth
|
|
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) |
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.
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?
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...
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.