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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
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:
|
1 |
SELECT Tools.Date$GetNthDay ('Tuesday', 0, '2021-02-17'); |
Returns: 2021-02-23
|
1 |
SELECT Tools.Date$GetNthDay ('Tuesday', 1, '2021-02-17'); |
Returns 2021-03-02
|
1 |
SELECT Tools.Date$GetNthDay ('Wednesday', 0, '2021-02-17'); |
Returns 2021-02-17 (since that is a Wednesday)
|
1 |
SELECT Tools.Date$GetNthDay ('Wednesday', -3, '2021-02-17'); |
Returns 3 weeks ago Wednesday or 2021-01-27
And a couple of fun tests
|
1 |
SELECT Tools.Date$GetNthDay ('Wednesday', -1000, '2021-02-17'); |
Returns 2001-12-19
|
1 |
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.
Load comments