Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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,
}

alt text

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.

asked 07 Oct '20, 12:13

Rolle's gravatar image

Rolle
558495161
accept rate: 0%

edited 07 Oct '20, 12:16

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.

(07 Oct '20, 15:32) Volker Barth

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?

(07 Oct '20, 15:42) Rolle
Replies hidden

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...

(07 Oct '20, 15:49) Volker Barth

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.

(07 Oct '20, 15:53) Rolle

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
permanent link

answered 09 Oct '20, 03:53

Volker%20Barth's gravatar image

Volker Barth
40.4k364553825
accept rate: 34%

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:

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.

(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:

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 .

(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)

permanent link

answered 08 Oct '20, 07:29

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k364766
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106

question asked: 07 Oct '20, 12:13

question was seen: 1,330 times

last updated: 30 Nov '20, 15:41