patternsqlMinor
Execute every Monday that is not the Monday following last Saturday in month
Viewed 0 times
lasttheeveryfollowingthatmonthmondaysaturdaynotexecute
Problem
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
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;Solution
Sometimes, comments aren't the clearest way to make a script readable:
Consider:
Does one need a comment to figure out what's going on? I hope not!
This part however:
If it wasn't of the
Why not just do this instead?
I mean, why work with a
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
...is illegal because...
I would extract that bit of logic into its own variable, for readability's sake, and using a
IF (
(--is it a monday?
SELECT DATEPART(dw,@now)
) = 2Consider:
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.Code Snippets
IF (
(--is it a monday?
SELECT DATEPART(dw,@now)
) = 2declare @MONDAY int;
set @MONDAY = 2;
if (datepart(weekday,@now) = @MONDAY)DECLARE @now DATETIME = DATEADD(dd,DATEDIFF(dd,'19000101',SYSDATETIME()),'19000101');declare @now date = cast(getdate() as date);DATEDIFF(DAY, @now-2, DATEADD(MONTH,DATEDIFF(MONTH,0,@now-2)+1,0)-1Context
StackExchange Code Review Q#114661, answer score: 7
Revisions (0)
No revisions yet.