In SQL Statement in microsoft sql server, there is a built-in function to get week number but it is the week of the year.
Select DatePart(week, '2012/11/30') // **returns 48**
The returned value 48 is the week number of the year.
Instead of 48, I want to get 1, 2, 3 or 4 (week number of the month). I think the week number of the month can be achieved by modules with Month Number of this week. For e.g.
Select DATEPART(week, '2012/11/30')%MONTH('2012/11/30')
But I want to know is there other built-in functions to get WeekNumber of the month in MS SQL SERVER.
This question is related to
sql-server-2008
tsql
There is an inbuilt option to get the week number of the year
**select datepart(week,getdate())**
declare @end_date datetime = '2019-02-28';
select datepart(week, @end_date) - datepart(week, convert(datetime, substring(convert(nvarchar, convert(datetime, @end_date), 127), 1, 8) + '01')) + 1 [Week of Month];
A dirty but easy one liner using Dense_Rank function. Performance WILL suffer, but effective none the less.
DENSE_RANK()over(Partition by Month(yourdate),Year(yourdate) Order by Datepart(week,yourdate) asc) as Week
Solution:
declare @dt datetime='2018-03-31 05:16:00.000'
IF (Select (DatePart(DAY,@dt)%7))>0
Select (DatePart(DAY,@dt)/7) +1
ELSE
Select (DatePart(DAY,@dt)/7)
Here is the query that brings the week number on whatever the startday and endday of the week it may be.
SET DATEFIRST 2
DECLARE @FROMDATE DATE='12-JAN-2015'
-- Get the first day of month
DECLARE @ALLDATE DATE=DATEADD(month, DATEDIFF(month, 0, @FROMDATE), 0)
DECLARE @FIRSTDATE DATE
;WITH CTE as
(
-- Get all dates in that month
SELECT 1 RNO,CAST(@ALLDATE AS DATE) as DATES
UNION ALL
SELECT RNO+1, DATEADD(DAY,1,DATES )
FROM CTE
WHERE DATES < DATEADD(MONTH,1,@ALLDATE)
)
-- Retrieves the first day of week, ie, if first day of week is Tuesday, it selects first Tuesday
SELECT TOP 1 @FIRSTDATE = DATES
FROM CTE
WHERE DATEPART(W,DATES)=1
SELECT (DATEDIFF(DAY,@FIRSTDATE,@FROMDATE)/7)+1 WEEKNO
For more information I have answered for the below question. Can check that.
select @DateCreated, DATEDIFF(WEEK, @DateCreated, GETDATE())
WeekMonth = CASE WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))>= 22 THEN '5'
WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))>= 15 THEN '4'
WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))>= 8 THEN '3'
WHEN (DATEPART(day,TestDate) - datepart(dw,TestDate))>= 1 THEN '2'
ELSE '1'
END
DECLARE @DATE DATETIME
SET @DATE = '2013-08-04'
SELECT DATEPART(WEEK, @DATE) -
DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,@DATE), 0))+ 1 AS WEEK_OF_MONTH
There is no inbuilt function to get you the week number. I dont think dividing will help you anyway as the number of weeks in a month is not constant.
http://msdn.microsoft.com/en-us/library/bb675168.aspx
I guess you can divide the number(48) by 4 and take the modules of the same and project that as the week number of that month, by adding one to the result.
You can simply get week number by getting minimum week number of month and deduct it from week number. Suppose you have a table with dates
select
emp_id, dt , datepart(wk,dt) - (select min(datepart(wk,dt))
from
workdates ) + 1 from workdates
Here you go....
Im using the code below..
DATEPART(WK,@DATE_INSERT) - DATEPART(WK,DATEADD(DAY,1,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DATE_INSERT),0)))) + 1
Check this out... its working fine.
declare @date as datetime = '2014-03-10'
select DATEPART(week,@date) - DATEPART(week,cast(cast(year(@date) as varchar(4))+'-' + cast(month(@date) as varchar(2)) + '-01' as datetime))+1
Just look at the date and see what range it falls in.
Range 1-7 is the 1st week, Range 8-14 is the 2nd week, etc.
SELECT
CASE WHEN DATEPART(day,yourdate) < 8 THEN '1'
ELSE CASE WHEN DATEPART(day,yourdate) < 15 then '2'
ELSE CASE WHEN DATEPART(day,yourdate) < 22 then '3'
ELSE CASE WHEN DATEPART(day,yourdate) < 29 then '4'
ELSE '5'
END
END
END
END
Logic here works as well 4.3 weeks in every month. Take that from the DATEPART(WEEK) on every month but January. Just another way of looking at things. This would also account for months where there is a 5th week
DECLARE @date VARCHAR(10)
SET @date = '7/27/2019'
SELECT CEILING(DATEPART(WEEK,@date)-((DATEPART(MONTH,@date)-1)*4.3333)) 'Week of Month'
Try Below Code:
declare @dt datetime='2018-03-15 05:16:00.000'
IF (Select (DatePart(DAY,@dt)%7))>0
Select (DatePart(DAY,@dt)/7) +1
ELSE
Select (DatePart(DAY,@dt)/7)
Code is below:
set datefirst 7
declare @dt datetime='29/04/2016 00:00:00'
select (day(@dt)+datepart(WEEKDAY,dateadd(d,-day(@dt),@dt+1)))/7
floor((day(@DateValue)-1)/7)+1
Here's a suggestion for getting the first and last days of the week for a month:
-- Build a temp table with all the dates of the month
drop table #tmp_datesforMonth
go
declare @begDate datetime
declare @endDate datetime
set @begDate = '6/1/13'
set @endDate = '6/30/13';
WITH N(n) AS
( SELECT 0
UNION ALL
SELECT n+1
FROM N
WHERE n <= datepart(dd,@enddate)
)
SELECT DATEADD(dd,n,@BegDate) as dDate
into #tmp_datesforMonth
FROM N
WHERE MONTH(DATEADD(dd,n,@BegDate)) = MONTH(@BegDate)
--- pull results showing the weeks' dates and the week # for the month (not the week # for the current month)
select MIN(dDate) as BegOfWeek
, MAX(dDate) as EndOfWeek
, datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, dDate), 0)), 0), dDate) as WeekNumForMonth
from #tmp_datesforMonth
group by datediff(week, dateadd(week, datediff(week, 0, dateadd(month, datediff(month, 0, dDate), 0)), 0), dDate)
order by 3, 1
No built-in function. It depends what you mean by week of month. You might mean whether it's in the first 7 days (week 1), the second 7 days (week 2), etc. In that case it would just be
(DATEPART(day,@Date)-1)/7 + 1
If you want to use the same week numbering as is used with DATEPART(week,), you could use the difference between the week numbers of the first of the month and the date in question (+1):
(DATEPART(week,@Date)- DATEPART(week,DATEADD(m, DATEDIFF(m, 0, @Date), 0))) + 1
Or, you might need something else, depending on what you mean by the week number.
Similar to the second solution, less code:
declare @date datetime = '2014-03-31'
SELECT DATEDIFF(week,0,@date) - (DATEDIFF(week,0,DATEADD(dd, -DAY(@date)+1, @date))-1)
Here is the tried and tested solution for this query in any situation - like if 1st of the month is on Friday , then also this will work -
select (DATEPART(wk,@date_given)-DATEPART(wk,dateadd(d,1-day(@date_given),@date_given)))+1
above are some solutions which will fail if the month's first date is on Friday , then 4th will be 2nd week of the month
Source: Stackoverflow.com