[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.

 

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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
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
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

 

About Me

Bruno Bragatto has an Associate in Applied Science Degree in Industrial Automation from IFSP (São Paulo – Brazil), a Master Degree in Database Administration – FIAP (São Paulo – Brazil) and is also an Oracle Database 11g Administrator Certified Associate and a Microsoft MCSA – SQL 2016 Database Administration.


OCA - Oracle 11g Database Administrator