[SCRIPT] – Date Condition using the DATENAME Function

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