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. 

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

Returns: 2021-02-23

Returns 2021-03-02

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

Returns 3 weeks ago Wednesday or 2021-01-27

And a couple of fun tests

Returns 2001-12-19

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.