Get First day and last day of week of any given day/date of the month.

• Syed Asrar Ali

  Mr or Mrs. 500

  Points: 517

  More actions

  April 12, 2012 at 6:43 am

  #392004

  Can anyone help me do this quickly in TSQL?

  i will post the code if i get it ready before you.:-)

  Thanks

  Syed Asrar Ali

• Mike01

  SSChampion

  Points: 11381

  More actions

  April 12, 2012 at 6:48 am

  #1472529

  It depends on what you mean by First Day and Last Day of week. This code assumes the first day of the week is Sunday and the last is Saturday

  declare @Date datetime

  set @Date = '2012-04-12'

  select @Date - DATEPART(dw, @Date) + 1 FirstDateOfWeek,

  @Date + (7 - DATEPART(dw, @Date)) LastDateOfWeek

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Syed Asrar Ali

  Mr or Mrs. 500

  Points: 517

  More actions

  April 12, 2012 at 6:50 am

  #1472531

  Thanks very much..:-)

  i came up is quite huge

  declare @start_day_week date

  declare @End_Day_week date

  if(dateadd(WEEK,0,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))<GETDATE() and dateadd(WEEK,1,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))-1>GETDATE())

  begin

  select @start_day_week =dateadd(WEEK,0,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))

  select @End_Day_week=dateadd(WEEK,1,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))-1

  end

  else

  if(dateadd(WEEK,1,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))<GETDATE() and dateadd(WEEK,2,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))-1>GETDATE())

  begin

  select @start_day_week =dateadd(WEEK,1,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))

  select @End_Day_week=dateadd(WEEK,2,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))-1

  end

  else

  if(dateadd(WEEK,2,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))<GETDATE() and dateadd(WEEK,3,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))-1>GETDATE())

  begin

  select @start_day_week =dateadd(WEEK,2,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))

  select @End_Day_week=dateadd(WEEK,3,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))-1

  end

  else

  if(dateadd(WEEK,3,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))<GETDATE() and dateadd(WEEK,4,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))-1>GETDATE())

  begin

  select @start_day_week =dateadd(WEEK,3,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))

  select @End_Day_week=dateadd(WEEK,4,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))-1

  end

  else

  begin

  select @start_day_week =dateadd(WEEK,4,DATEADD(MOnth,DATEDIFF(month,'1990',GETDATE()),'1990'))

  select @End_Day_week=dateadd(month,1,dateadd(month,DATEdiff(month,'1990',getdate()),'1990'))-1

  end

  select @start_day_week

  select @End_Day_week

  😀

• Syed Asrar Ali

  Mr or Mrs. 500

  Points: 517

  More actions

  April 12, 2012 at 6:52 am

  #1472532

  Thank for the help Mike01. i was not aware of this.:-)