August 6, 2004 at 10:06 am
Has anyone run across TSQL syntax for returning date of the nth Friday of a given month or something similar?
Carl
August 6, 2004 at 12:30 pm
Carl,
Here's a script that I already had in my archive. It goes the other way, but you should be able to tweak it to get what you need.
Steve
declare
@date datetime,
@num int,
@result int,
@txt2 varchar(9),
@txt1 char(3),
@txt3 varchar(80)
set @date = '2004-11-28'
select @num = datepart(dd, @date)
if (@num % 7) <> 0
set @result = (@num / 7) + 1
else
set @result = (@num / 7)
select @txt1 =
case
when @result = 1 then '1st'
when @result = 2 then '2nd'
when @result = 3 then '3rd'
when @result = 4 then '4th'
when @result = 5 then '5th'
end
set datefirst 7
select @txt2 = datename(dw, @date)
set @txt3 = @txt1 + ' ' + @txt2
print @txt3
August 9, 2004 at 2:41 am
How about this :
Create Function dbo.udf_Get_Nth_DayNo_After
( @Start_DateTime AS datetime
, @End_DateTime AS datetime = '9999-12-31'
, @SearchDayNr as tinyint = 1
, @nth as tinyint = 1
, @hh_time as char(2) = '00')
returns datetime
As
BEGIN
-- ALZDBA
-- Determine n-th daynumber x after Start_DateTime and before or equal to End_DateTime
-- Remark: Sunday is day 1 ... unless you use SET DATEFIRST { number | @number_var } !
if @Start_DateTime > @End_DateTime
begin
return(null)
end
if @SearchDayNr between 1 and 7
begin
set @SearchDayNr = @SearchDayNr
end
else
begin
return(null)
end
Declare @DateFirstSearchDay as datetime
Declare @Dw_Start_DateTime as smallint
Set @Dw_Start_DateTime = DATEPART(dw,@Start_DateTime)
if @Dw_Start_DateTime = @SearchDayNr
begin
set @DateFirstSearchDay = convert(datetime,convert(varchar(11),@Start_DateTime,121) + @hh_time + ':00:00')
end
else
begin
set @DateFirstSearchDay = convert(datetime,convert(varchar(11),dateadd(dd, 7 - @Dw_Start_DateTime + @SearchDayNr ,@Start_DateTime),121) + @hh_time + ':00:00')
end
-- print convert(varchar(25),@DateFirstSearchDay,121)
Declare @DateNthSearchDay as datetime
set @DateNthSearchDay = dateadd(dd,(@Nth * 7 ), @DateFirstSearchDay)
if @DateNthSearchDay > @End_DateTime
begin
-- print 'Nth-Searchday after ' + convert(varchar(25),@End_DateTime,121)
return(NULL)
end
-- print (@DateNthSearchDay)
return(@DateNthSearchDay)
END
go
GRANT EXECUTE ON dbo.udf_Get_Nth_DayNo_After TO public
GO
declare @Start_DateTime AS DateTime
, @End_DateTime AS DateTime
, @SearchDayNr as tinyint
, @nth as tinyint
, @hh_time as char(2)
Select @Start_DateTime = '2004-04-02'
, @End_DateTime = '2004-05-04'
, @SearchDayNr = 1
, @nth = 3
, @hh_time = '02'
-- find 3-th sunday after 2004-04-01 and before 2004-05-04 (should be 2004-04-18)
Select dbo.udf_Get_Nth_DayNo_After( @Start_DateTime, @End_DateTime, @SearchDayNr, @nth , @hh_time )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 9, 2004 at 3:29 am
Or this:
declare @year int
declare @month int
declare @day_of_week int
declare @nth_week int
declare @nth_day_of_month datetime
declare @first_of_month char(10)
declare @days_to_first_day int
declare @day_first_of_month int
--fill in the required vars
set @year = 2004
set @month = 8
set @day_of_week = 1
set @nth_week = 2
if (@day_of_week > 0 and @day_of_week < 8)
and (@month > 0 and @month < 13)
and (@year > 0 and @year < 9999)
and (@nth_week > 0 and @nth_week < 6)
begin
set @first_of_month = cast(@month as nvarchar(2)) + '/01/' + cast(@year as nvarchar(4))
set @day_first_of_month = datepart(dw,@first_of_month)
set @days_to_first_day =
(
select case
when @day_of_week < @day_first_of_month
then (7-@day_first_of_month) + @day_of_week
when @day_of_week > @day_first_of_month
then @day_of_week - @day_first_of_month
else
0
end)
set @nth_day_of_month = cast(@first_of_month as datetime)
set @nth_day_of_month = @nth_day_of_month + @days_to_first_day
if @nth_week > 1
begin
set @nth_day_of_month = @nth_day_of_month + (7 * (@nth_week - 1))
end
print @nth_day_of_month
end
else
print 'wrong input'
Regards,
Leon Bakkers
August 9, 2004 at 6:07 am
There's a function that will do what you want at this link:
http://www.novicksoftware.com/UDFofWeek/Vol2/T-SQL-UDF-Vol-2-Num-14-udf_DT_NthDayInMon.htm
Regards,
Andy
August 9, 2004 at 5:34 pm
-- Short and Efficient version
--External Parameters---
declare @Month varchar(2)
declare @Year varchar(4)
declare @n int
--End External Parameters---
----------------------------
--Test Values
Set @n = 3
Set @Year = '2004'
Set @Month = '08'
----------------------------
declare @Date_str varchar(8)
declare @dt_temp as datetime
declare @num as int
declare @nthFriday datetime
Set @Date_str = @Year + @Month + '01'
set @dt_temp = convert(datetime,@Date_str,112)
set @num = (@@DATEFIRST - Day(@dt_temp) -1) + 7*(@n-1)
set @nthFriday = dateadd(d,@num, @dt_temp)
if (isdate(@nthFriday) = 1 and Month(@nthFriday) = Month(@dt_temp))
print @nthFriday
else
print 'n is not valid'
August 11, 2004 at 10:08 am
Thanks to all for your help - I appreciate it very much!
Carl
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply