Home > Forum > Rules, JS, SQL > How to return weekend days in SQL?

How to return weekend days in SQL?
0

Hi

I have a question. I want to return all weekend days in SQL for my form validation.
This is my SQL Code that returns these days, but only for the current month:

WITH Dates AS (
SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) AS DateValue
UNION ALL
SELECT DATEADD(DAY, 1, DateValue)
FROM Dates
WHERE DATEADD(DAY, 1, DateValue) < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
)
SELECT DateValue
FROM Dates
WHERE DATEPART(WEEKDAY, DateValue) IN (1, 7)
OPTION (MAXRECURSION 0);

It only returns the weekends in the current month (July). But that scenario happens only in SSMS.
Webcon has problem reading it and won't make it work.
What I want to achieve is to do an If statement inside style and behaviour to check whether the day provided in the form is a weekend or not.
If is the weekend, then false, if not then true.

MVP

Hi,

if you want to use it in the style and behavior, I would use a form rule in JavaScript mode.

This form rule can then be used for example in a condition (1) or for setting a field (2).

This is the JS code and you would need to change the parameter of course.
//debugger;
// Step 1: Convert the date string to a Date object
const date = new Date(#{BRP:102}#);

// Step 2: Get the day of the week (0 - Sunday, 1 - Monday, ..., 6 - Saturday)
const dayOfWeek = date.getDay();

// Step 3: Check if the day is Saturday (6) or Sunday (0)
return dayOfWeek === 0 || dayOfWeek === 6;

Best regards,
Daniel

MVP
In reply to: Igor Sobolewski

And here's the code:

I don't know why but for me the JavaScript form rule is also working fine in item lists.

I only made two small mistakes when I added it:
- I did select the "Date" field and not the column.
- During my tests I didn't looked at the selected date in the picker. The Sunday / Saturday have been not where I expected them to be in the picker. :)


A little information, why I suggested the JavaScript instead of the business rule.

I had some bad experience with determining the day of a week with SQL as it depends on the culture of the server or something alike. There's a workaround for this but in JavaScript no workaround is required for it. At least as far as I know. :)

MVP
In reply to: Igor Sobolewski

I did it on Business Rule :)

Check this out. Is Weekend in my case is 'Czy Weekend'

You used the DATEPART parameters (1, 7), which follow the US format, as mentioned in my first post.
If you only tested Saturdays, your business rule worked fine.

Please test Sundays or change the parameters to (6, 7).

Otherwise, in my opinion, the date field must be EMPTY while testing.

Strange,
Bjoern