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);
No comments:
Post a Comment