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\u00a0programmer doesn’t do repetitive work when code can be written… even if sometimes the code doesn’t save you time for days or weeks.<\/p>\n
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):<\/p>\n
NOTE: The code is available in my Github repository for my database design book here: dbdesignbook6\/Useful Code at master \u00b7 drsqlgithub\/dbdesignbook6<\/a> in the Tools Schema.sql<\/a> file.\u00a0<\/p>\n To see the code in work, Here are a few examples:<\/p>\n Returns: 2021-02-23<\/p>\n Returns 2021-03-02<\/p>\n Returns 2021-02-17 (since that is a Wednesday)<\/p>\n Returns 3 weeks ago Wednesday or 2021-01-27<\/p>\n And a couple of fun tests<\/p>\n Returns 2001-12-19<\/p>\n Returns 3937-09-01<\/p>\n 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 \u00b7 drsqlgithub\/sqlprompt<\/a> and it is named: LD_GetNthDay.sqlpromptsnippet<\/a>.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" 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……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,1],"tags":[],"coauthors":[19684],"class_list":["post-89962","post","type-post","status-publish","format-standard","hentry","category-blogs","category-uncategorized"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89962","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=89962"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89962\/revisions"}],"predecessor-version":[{"id":89963,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/89962\/revisions\/89963"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=89962"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=89962"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=89962"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=89962"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}CREATE OR ALTER FUNCTION Tools.Date$GetNthDay\r\n(\r\n --Spelled out the name of the day of the week\r\n\t@DayName varchar(20), \r\n --positive or negative offset from the current week\r\n\t@NumberOfWeeks int = 0, \r\n\t@DateValue date = NULL --the day to start the calculation\r\n)\r\nRETURNS date\r\nAS\r\n BEGIN \r\n --if the date parameter is NULL, use current date\r\n SET @dateValue = COALESCE(@DateValue,SYSDATETIME());\r\n\r\n --this is a stand in for a calendar table to make it portable and not subject\r\n --to any date settings\r\n DECLARE @DaysOfWeek table (DayNumber int NOT NULL, \r\n DayName varchar(20) NOT NULL);\r\n\r\n --load 14 days to make the math of days between days easy\r\n INSERT INTO @DaysOfWeek(DayNumber, DayName)\r\n VALUES(1,'Sunday'),(2,'Monday'),(3,'Tuesday'),\r\n (4,'Wednesday'), (5,'Thursday'),(6,'Friday'),\r\n (7,'Saturday'), (8,'Sunday'),(9,'Monday'),\r\n (10,'Tuesday'),(11,'Wednesday'), (12,'Thursday'),\r\n (13,'Friday'),(14,'Saturday');\r\n\r\n --get the day number of the date that was passed in \r\n --on the DateValue parameter\r\n DECLARE @CurrentDayNumber int = (SELECT MIN(DayNumber) \r\n\t FROM @DaysOfWeek \r\n WHERE DayName = DATENAME(weekday, @DateValue)); \r\n\r\n --get the next day number in the table to get the number \r\n --of days to add\r\n DECLARE @NextDayNumber int = (SELECT MIN(DayNumber) \r\n FROM @DaysOfWeek \r\n\t\t\t\t WHERE DayName = @DayName \r\n\t\t\t\t AND DayNumber >= @CurrentDayNumber); \r\n\r\n --add the number of weeks to the date you calculate to be \r\n --the upcoming day that matched your parameters\r\n RETURN (DATEADD(WEEK,@NumberOfWeeks,\r\n DATEADD(DAY, @NextDayNumber \u2013 @CurrentDayNumber, @DateValue)));\r\n END;\r\nGO<\/pre>\n
SELECT Tools.Date$GetNthDay ('Tuesday', 0, '2021-02-17');<\/pre>\nSELECT Tools.Date$GetNthDay ('Tuesday', 1, '2021-02-17');<\/pre>\nSELECT Tools.Date$GetNthDay ('Wednesday', 0, '2021-02-17');<\/pre>\nSELECT Tools.Date$GetNthDay ('Wednesday', -3, '2021-02-17');<\/pre>\nSELECT Tools.Date$GetNthDay ('Wednesday', -1000, '2021-02-17');<\/pre>\nSELECT Tools.Date$GetNthDay ('Wednesday', 100000, '2021-02-17');<\/pre>\n