01/01/2017
--------Get Month and Week and Last day in this month In SQl server database
declare DATE = '2016-12-01 00:00:00.000'
declare DATE = '2017-01-08 00:00:00.000';
;with months (date)
AS
(
SELECT
UNION ALL
SELECT DATEADD(month,1,date)
from months
where DATEADD(month,1,date) <
)
select [MonthName] = DATENAME(mm ,Date)
,[MonthNumber] = DATEPART(mm ,Date)
,[LastDayOfMonth] = DATEPART(dd,EOMONTH(Date))
,[StartDaye] = DATENAME(DW,DATEADD(DW,1,EOMONTH(Date,-1)))
,[LastDaye] = DATENAME(DW,EOMONTH(Date))
,[MonthYear] = DATEPART(yy ,Date)
from months s
