SQL Server - Calender details

Below T-SQL script using common table expression will provide the calender details:


DECLARE @StartDate DATE = '2014-01-01', @EndDate DATE = '2014-12-31' 
;With CTE
AS
(
SELECT @StartDate dt 
UNION ALL 
SELECT DATEADD(DAY, 1, dt) FROM Cte
WHERE dt < @EndDate
)
SELECT 
FORMAT ( dt, 'D', 'en-US' ) Calender
,DATENAME(dayofyear, dt) DayNumber_Year
,DATEPART(day, dt) DayNumber_Month
,DATEPART(weekday, dt) DayNumber_Week
,DATENAME(weekday, dt) DayName
,DATEPART(WEEK,dt) WeekNumber
,IIF ( DATEPART(weekday, dt) IN (1,7) , 'Weekend', 'Weekday' ) Day
,DATENAME(month, dt) MonthName
,DATEPART(month, dt) MonthNumber
,DATENAME(year, dt) Year
,EOMONTH (dt) MonthLastDate
FROM cte
OPTION (MAXRECURSION 366);








See Also:





No comments: