Sometimes it’s necessary to create a Condition based in specific Date (like first day of the year, fifth day of the month, the second Monday of the month etc… ).
I’ve been in so many situations like that and most of the time the solution was to use a scheduler, SQL Agent Job or so many other tools to manage those cases. It’s valid, obviously.
But what if we could launch a single job and the Stored Procedure or the script manage this internally? It could be easier to administrate, manage and maintain.
My solution for that was to use the DATENAME function. I won’t get into the details of this function, but you may want to check the documentation to be familiarized with it and try with something like DATENAME(DW, GETDATE()) for example.
I will instead explain my example and hopefully this could be a nice introduction for the solution that you want to get with this.
It was declared 4 variables: @dayMonth as Varchar(4), @dayName as Varchar(10), @dayYear as Varchar(4) and @TestDate as datetime.
Then I set them as:
SET @TestDate = GETDATE() –’12/31/2022′ Just a getdate or if you want to test a specific date.
SET @dayName = DATENAME(DW, @TestDate); –We want to look here the name of the day like Sunday
SET @dayMonth = DATENAME (DD, @TestDate); — Here we want the day of the month, like the first.
SET @dayYear = DATENAME(DY, @TestDate); –Here we want to get the day of the year like the first for example.
DECLARE @dayMonth VARCHAR(4) DECLARE @dayName VARCHAR(10); DECLARE @dayYear VARCHAR(4); DECLARE @TestDate datetime --VARCHAR (24) SET @TestDate = GETDATE() --'12/31/2022' --GETDATE() SET @dayName = DATENAME(DW, @TestDate); SET @dayMonth = DATENAME (DD, @TestDate); SET @dayYear = DATENAME(DY, @TestDate); /* SET @dayName = DATENAME(DW, GETDATE()); SET @dayMonth = DATENAME (DD, GETDATE()); SET @dayYear = DATENAME(DY, GETDATE()); */ IF @dayYear = 1 BEGIN PRINT 'YEARLY' PRINT @TestDate PRINT @dayName PRINT @dayMonth PRINT @dayYear END IF @dayMonth=1 and @dayYear<>1 BEGIN PRINT 'MONTHLY' PRINT @TestDate PRINT @dayName PRINT @dayMonth PRINT @dayYear END IF @dayName='Sunday' and @dayMonth<>1 BEGIN PRINT 'WEEKLY' PRINT GETDATE() PRINT @TestDate PRINT @dayName PRINT @dayMonth PRINT @dayYear END IF @dayName<>'Sunday' and @dayMonth<>1 BEGIN PRINT 'DAILY' PRINT @TestDate PRINT @dayName PRINT @dayMonth PRINT @dayYear END