Calculating the Second Sunday jus giving the month alone

• lefrancisco1

  Say Hey Kid

  Points: 704

  More actions

  August 26, 2009 at 11:49 pm

  #209269

  Folks,

  Hwo to Calculate the second sunday of the particular month

  Example -- the user input is month alone like... 08 the second sunday is 09 Likewise.

  Can u please help me out with this.

  Happy Querying

• arun.sas

  SSChampion

  Points: 11831

  More actions

  August 27, 2009 at 1:10 am

  #1045353

  Hi,

  Just modify David Burrows coding posted in the same question early

  SET DATEFIRST 1

  CREATE function get_2_sunday

  (

  @month int,

  @year int

  )

  RETURNS INT

  as

  begin

  declare @day int

  SELECT @day = DAY(DATEADD(day,7+7-DATEPART(dw,(cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+'01')),

  (cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+'01')))

  RETURN ( @day )

  END

  select dbo.get_2_sunday (2,2008)

• lefrancisco1

  Say Hey Kid

  Points: 704

  More actions

  August 27, 2009 at 1:22 am

  #1045357

  David,

  It was returning an error

  ERROR -Msg 102, Level 15, State 1, Line 1

  Incorrect syntax near '8'.

  while executing the function

• arun.sas

  SSChampion

  Points: 11831

  More actions

  August 27, 2009 at 1:32 am

  #1045362

  Hi,

  complie this statement

  and getback with the result

  declare @month int,@year int,@DATE varchar(15)

  select @month = 8,@year = 2009

  set @DATE = (cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+'01')

  select @DATE

  SELECT DAY(DATEADD(day,7+7-DATEPART(dw,(cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+'01')),

  (cast(@year as varchar)+'-'+cast(@month as varchar)+'-'+'01')))