Execute every Monday that is not the Monday following last Saturday in month

Question

I think the following is good but wondering if it can be simplified or improved.

My condition for code execution is the following:

It is a Monday AND It is not the Monday following the last Saturday in the month.

This is the current sql:

DECLARE @now DATETIME = DATEADD(dd,DATEDIFF(dd,'19000101',SYSDATETIME()),'19000101');


IF (
        (--is it a monday?
            SELECT DATEPART(dw,@now)
        ) = 2  

        AND

        (--is it not the monday following the last Saturday of the month?
            (
            SELECT DATEDIFF(
                            DAY,
                            @now-2, 
                            DATEADD(MONTH,DATEDIFF(MONTH,0,@now-2)+1,0)-1 
                        )
            ) > 6
        )
    )
BEGIN;

  ...
  ...

END;

Answer 1

Sometimes, comments aren't the clearest way to make a script readable:

IF (
        (--is it a monday?
            SELECT DATEPART(dw,@now)
        ) = 2

Consider:

declare @MONDAY int;
set @MONDAY = 2;

if (datepart(weekday,@now) = @MONDAY)

Does one need a comment to figure out what's going on? I hope not!

This part however:

DECLARE @now DATETIME = DATEADD(dd,DATEDIFF(dd,'19000101',SYSDATETIME()),'19000101');

If it wasn't of the @now identifier, I'd wonder what this convoluted assignment intends to do.

Why not just do this instead?

declare @now date = cast(getdate() as date);

I mean, why work with a datetime when clearly you're only interested in the date part from that point on?

---

I find there's way too much whitespace in your code, you need to strike a balance between wall-of-code and air code.

Now, changing @now from a datetime to a date will break your code. The @now-2 here:

DATEDIFF(DAY, @now-2, DATEADD(MONTH,DATEDIFF(MONTH,0,@now-2)+1,0)-1

...is illegal because...

I would extract that bit of logic into its own variable, for readability's sake, and using a dateadd function there would be no issue with subtracting 2 days from that date.

Answer 2

Following Mat's Mug's advice I have the following

DECLARE @now DATE = DATEADD(dd,DATEDIFF(dd,'19000101',SYSDATETIME()),'19000101');


DECLARE @MONDAY INT = 2;
DECLARE @dayBeforeYESTERDAY DATE = DATEADD(dd,-2,@now);
DECLARE @LastDayOfMthfor_dayBeforeYESTERDAY DATE = DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dayBeforeYESTERDAY)+1,0));

IF (

        (SELECT DATEPART(dw,@now)) = @MONDAY        
        AND
        (
            --check to see if it is not following the last Saturday in month
            (SELECT DATEDIFF(DAY,@dayBeforeYESTERDAY, @LastDayOfMthfor_dayBeforeYESTERDAY))
            > 6
        )
    )
BEGIN;
...
...
END;