Function (and SQL Prompt Snippet) To Get Next Date By Day Name/Offset

As I have been building my Twitter management software, I have been doing a lot more ad-hoc, repetitive coding using T-SQL directly. When I was generating new tweets for upcoming days, a bit of the process that got old quick was getting the date for an upcoming day (the primary key for my tweet table is the date, the type of the tweet, and a sequence number). After having to pick the date of next Tuesday… I had to write some more code (because a true programmer doesn’t do repetitive work when code can be written… even if sometimes the code doesn’t save you time for days or weeks.

So this following function was born from that need, and it is something I could imagine most anyone using semi-regularly, especially when testing software. This is the code (with a bit of reformatting):

NOTE: The code is available in my Github repository for my database design book here: dbdesignbook6/Useful Code at master · drsqlgithub/dbdesignbook6 in the Tools Schema.sql file. 

CREATE OR ALTER FUNCTION Tools.Date$GetNthDay
(
     --Spelled out the name of the day of the week
        @DayName varchar(20), 
     --positive or negative offset from the current week
        @NumberOfWeeks int = 0, 
        @DateValue date = NULL --the day to start the calculation
)
RETURNS date
AS
 BEGIN 
    --if the date parameter is NULL, use current date
    SET @dateValue = COALESCE(@DateValue,SYSDATETIME());

    --this is a stand in for a calendar table to make it portable and not subject
    --to any date settings
    DECLARE @DaysOfWeek table (DayNumber int NOT NULL, 
                               DayName varchar(20) NOT NULL);

    --load 14 days to make the math of days between days easy
    INSERT INTO @DaysOfWeek(DayNumber, DayName)
    VALUES(1,'Sunday'),(2,'Monday'),(3,'Tuesday'),
           (4,'Wednesday'), (5,'Thursday'),(6,'Friday'),
           (7,'Saturday'), (8,'Sunday'),(9,'Monday'),
           (10,'Tuesday'),(11,'Wednesday'), (12,'Thursday'),
           (13,'Friday'),(14,'Saturday');

     --get the day number of the date that was passed in 
     --on the DateValue parameter
     DECLARE @CurrentDayNumber int = (SELECT MIN(DayNumber) 
                                      FROM @DaysOfWeek 
                                      WHERE DayName = DATENAME(weekday, @DateValue));  

      --get the next day number in the table to get the number 
      --of days to add
      DECLARE @NextDayNumber int = (SELECT MIN(DayNumber) 
                                    FROM @DaysOfWeek 
                                    WHERE DayName = @DayName 
                                      AND DayNumber >= @CurrentDayNumber); 

     --add the number of weeks to the date you calculate to be 
     --the upcoming day that matched your parameters
      RETURN (DATEADD(WEEK,@NumberOfWeeks,
               DATEADD(DAY, @NextDayNumber – @CurrentDayNumber, @DateValue)));
 END;
GO

To see the code in work, Here are a few examples:

SELECT Tools.Date$GetNthDay ('Tuesday', 0, '2021-02-17');

Returns: 2021-02-23

SELECT Tools.Date$GetNthDay ('Tuesday', 1, '2021-02-17');

Returns 2021-03-02

SELECT Tools.Date$GetNthDay ('Wednesday', 0, '2021-02-17');

Returns 2021-02-17 (since that is a Wednesday)

SELECT Tools.Date$GetNthDay ('Wednesday', -3, '2021-02-17');

Returns 3 weeks ago Wednesday or 2021-01-27

And a couple of fun tests

SELECT Tools.Date$GetNthDay ('Wednesday', -1000, '2021-02-17');

Returns 2001-12-19

SELECT Tools.Date$GetNthDay ('Wednesday', 100000, '2021-02-17');

Returns 3937-09-01

In addition, to my Redgate tools repo, I added a snippet that does the same thing, but does not require the overhead of compiling code, which lets you use the code anywhere you desire. You can get that here in my Github repo for : sqlprompt/snippets at master · drsqlgithub/sqlprompt and it is named: LD_GetNthDay.sqlpromptsnippet.

 

The post Function (and SQL Prompt Snippet) To Get Next Date By Day Name/Offset appeared first on Simple Talk.

from Simple Talk https://ift.tt/2NhrrYH
via