how to get last date of month and week number

Question

Hi,

   I have 10 years of dates in one column X I want month end date in column Y , week number in column Z of that  related to each date in column X .

Answer 1

Try the below:

Create Table T11(Col1 Datetime)
Insert into T11 Select GETDATE()
SELECT Col1,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,Col1)+1,0)), DATEPART( wk, Col1 )
From T11

---

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 2

SQL Server 2012 has the EOMONTH() function:

-- Month end date
SELECT EOMONTH(getdate());
-- 2012-09-30
-- Month start date
SELECT DATEADD(DD,1,EOMONTH(getdate(),-1));
-- 2012-09-01

It can be used to calculate month start date also.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 3

Declare @Year Int, @Month Int,@Day Int

Select @Year = 2012,@Month = 2,@Day = 1 


Select DATEADD(dd,-1, DateAdd(M,1,Cast(Cast(@Year As varchar(4))+'-'+
	Cast(@Month As varchar(4))+'-01 00:00:00.001' As DateTime)))
	As LastDateOfMonth,
	 
	  DATEPART(wk,CAST(Cast(@Year As Varchar(4))
	  +'-'+Cast(@Month As Varchar(2))+'-'+Cast(@Day As Varchar(2))
	    As DateTime)) As WeekNoOfDate
	 

---

Please, If answer match your requirement then set mark as answer