Product:
Microsoft SQL server

Issue:

In a view of dates, we only want the 5 last years and the 5 future years, from today date. How do it?

Solution:

Create a view of the table listed above, where you define it like below:

CREATE VIEW [DM].[DMDateView]
AS
SELECT [key_dimDate]
,[DateIndex]
,[Date]
,[Day]
,[DaySuffix]
,[Weekday]
,[WeekDayName]
,[DOWInMonth]
,[DayOfYear]
,[WeekOfMonth]
,[WeekOfYear]
,[ISOWeekOfYear]
,[Month]
,[MonthName]
,[Quarter]
,[QuarterName]
,[Year]
,[MMYYYY]
,[MonthYear]
,[FirstDayOfMonth]
,[LastDayOfMonth]
,[FirstDayOfQuarter]
,[LastDayOfQuarter]
,[FirstDayOfYear]
,[LastDayOfYear]
,[FirstDayOfNextMonth]
,[FirstDayOfNextYear]
FROM [DM].[dimDate]
where 1=1
and [Date] BETWEEN DateAdd(yy, -5, GetDate()) AND DateAdd(yy, +5,GetDate())

 

 

More Information:

https://www.mickpatterson.com.au/blog/add-auto-increment-to-a-column-in-sql-server

https://www.w3schools.com/sql/func_sqlserver_dateadd.asp

https://www.dbvis.com/thetable/sql-server-dateadd-the-complete-guide/