Advertisement
elena1234

How to create Calendar Table ( T-SQL)

Mar 15th, 2022 (edited)
2,404
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.07 KB | None | 0 0
  1. --Create Table
  2.  
  3. CREATE TABLE Adventureworks2019.dbo.Calendar
  4. (
  5. DateValue DATE,
  6. DayOfWeekNumber INT,
  7. DayOfWeekName VARCHAR(32),
  8. DayOfMonthNumber INT,
  9. MonthNumber INT,
  10. YearNumber INT,
  11. WeekendFlag TINYINT,
  12. HolidayFlag TINYINT
  13. )
  14.  
  15.  
  16. --Insert values manually
  17.  
  18. INSERT INTO Adventureworks2019.dbo.Calendar
  19. (
  20. DateValue,
  21. DayOfWeekNumber,
  22. DayOfWeekName,
  23. DayOfMonthNumber,
  24. MonthNumber,
  25. YearNumber,
  26. WeekendFlag,
  27. HolidayFlag
  28. )
  29.  
  30. VALUES
  31. (CAST('01-01-2011' AS DATE),7,'Saturday',1,1,2011,1,1),
  32. (CAST('01-02-2011' AS DATE),1,'Sunday',2,1,2011,1,0)
  33.  
  34.  
  35. SELECT * FROM Adventureworks2019.dbo.Calendar
  36.  
  37.  
  38. --Truncate manually inserted values
  39.  
  40.  
  41. TRUNCATE TABLE Adventureworks2019.dbo.Calendar
  42.  
  43.  
  44. --Insert dates to table with recursive CTE
  45.  
  46. WITH Dates AS
  47. (
  48. SELECT
  49.  CAST('01-01-2011' AS DATE) AS MyDate
  50.  
  51. UNION ALL
  52.  
  53. SELECT
  54. DATEADD(DAY, 1, MyDate)
  55. FROM Dates
  56. WHERE MyDate < CAST('12-31-2030' AS DATE)
  57. )
  58.  
  59. INSERT INTO AdventureWorks2019.dbo.Calendar
  60. (
  61. DateValue
  62. )
  63. SELECT
  64. MyDate
  65.  
  66. FROM Dates
  67. OPTION (MAXRECURSION 10000)
  68.  
  69. SELECT * FROM AdventureWorks2019.dbo.Calendar
  70.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement