Nth Friday of the month

  • Has anyone run across TSQL syntax for returning date of the nth Friday of a given month or something similar?

    Carl

  • 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

  • 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 @Nth = @Nth - 1

     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

  • 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

  • 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

     

  • -- 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'

     

  • 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