CODEWITHSUNDEEP
sql-server
KellyK
KellyK

Reputation: 11

SQL Server : get day of month from Year Month WeekOfMonth

I have a complicated calendar report coming from a SQL query where I have the year, month and WeekNumOfMonth (meaning the week number from the beginning of the month).

I need a function to return the DayOfMonth (ie: values of between 1-28,29,30 or 31 depending on the month) given Year, month and WeekNumOfMonth.

I have seen functions that do this given WeekNum from beginning of year but I only have WeekNum of the given month. I just can't seem to give the datePart and dateAdd functions right. Any help would be appreciated.

Upvotes: 0

Views: 116

Answers (1)

Arunprasanth K V
Arunprasanth K V

Reputation: 21931

Note : Combination of Year ,Month ,Week Number will not give you the day name because a week contain more than one day so how can you find which day is it ? it is not possible

example : year 2015 month 06 week number 1

then it has a combination of

1 monday 
2  tuesday
3 wednesday
4 thursday
5 friday 
6 saturday

then with these results how can you predit the exact day ??

Else need some conditions or restrictions

If you have Year, Month , and date then you can find the day name using the below query it will give the start and end date of the week then you can take the dates from start date and end with end date

SELECT (
  DATENAME(dw, 
  CAST(DATEPART(m, GETDATE()) AS VARCHAR) 
  + '/' 
  + CAST(DATEPART(d, '2015-06-23') AS VARCHAR) 
  + '/' 
  + CAST(DATEPART(yy, getdate()) AS VARCHAR))
  )

Update

If you need all days between the current week then use the following query

DECLARE
    @Year INT,
    @Week INT,
    @FirstDayOfYear DATETIME,
    @FirstMondayOfYear DATETIME,
    @StartDate DATETIME,
    @EndDate DATETIME

SET @Year = 2015
SET @Week = 24// week number is based on year and it is not based on month

-- Get the first day of the provided year.
SET @FirstDayOfYear = CAST('1/1/' + CAST(@YEAR AS VARCHAR) AS DATETIME)

-- Get the first monday of the year, then add the number of weeks.
SET @FirstMondayOfYear = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6 - DATEPART(DAY, @FirstDayOfYear), @FirstDayOfYear)), 0)

SET @StartDate = DATEADD(WEEK, @Week - 1, @FirstMondayOfYear)

-- Set the end date to one week past the start date.
SET @EndDate = DATEADD(WEEK, 1, @StartDate)

SELECT @StartDate AS StartDate, DATEADD(SECOND, -1, @EndDate) AS EndDate

Update

if do not have the year week number then use the below query

DATEPART(WEEK,'2015-06-01')

The above query is one example

In the query you have two values that is Year and Month

you need only the third value that means date

as a technique you can give 01 as the date then it will give the starting week number your selected month

here it will result 23 so you got the idea in 2015 - month 06 starts with week number 23 so then you can easily do your stuff

Idea : if your week number is 1 then take 23 as week if it is 2 then 24 like wise..

Upvotes: 0

Related Questions