USE [Tools]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- select dbo.XrdDay(4,'thursday', 'november', 2018) -- Thanksgiving
-- select dbo.XrdDay(4,'thursday', 'november', 2019) -- Thanksgiving
-- select dbo.XrdDay(4,'thursday', 'november', 2020) -- Thanksgiving
-- Must have the day of week and the name of month spelled correctly.
create or alter function [dbo].[XrdDay](
@xrd int --3rd, 4th, 5th, etc.
,@dayname varchar(8) --'saturday'
,@month varchar(10) --'february'
,@year smallint --year
)
returns date
as
begin;
-- declare @xrd int = 3; declare @dayname varchar(8) = 'monday'; declare @month varchar(10) = 'september'; declare @year smallint = 2019;
declare @Result date= @month + ' ' + convert(varchar(64),(@xrd*7) - 6) + ',' + convert(varchar(64), @year); -- get Sunday.
--select @Result, datename(weekday, @Result);
--I'm fairly sure this is inefficient, but I can't seem to recall of another way around it.
--to make it better, we need to calc @sunday+int==dayname in one go, not this while loop.
while datename(weekday, @Result) collate SQL_Latin1_General_CP1_CI_AS != @dayname set @Result = dateadd( day, 1, @Result );
--select @Result, datename(weekday, @Result);
return @Result;
end;
Like this:
Like Loading...
Related
Published by Protiguous
C# Software Developer, SQL Server DBA, Father, and seeker of Truth.
View all posts by Protiguous