Tuesday, September 25, 2012 5:15 AM
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 .
- Moved by Tom PhillipsModerator Tuesday, September 25, 2012 2:17 PM TSQL question (From:SQL Server Database Engine)
Tuesday, September 25, 2012 5:24 AM
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!
Friday, September 28, 2012 11:51 PMModerator
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-01It can be used to calculate month start date also.
Saturday, September 29, 2012 4:22 AM
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