Advanced Search

mysql calendar table

/ Published in: SQL
Save to your folder(s)
creates a minimal calendar table

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. DROP TABLE IF EXISTS calendar_t;
  2. CREATE TABLE calendar_t (
  3.   id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
  4.   `date` DATE NOT NULL,
  5.   DAY VARCHAR(9) NOT NULL,
  6.   MONTH VARCHAR(13) NOT NULL,
  7.   `year` INTEGER NOT NULL
  8. );
  9.  
  10. DROP VIEW IF EXISTS digits_v;
  11. CREATE VIEW digits_v
  12. AS
  13. SELECT 0 AS n
  14. UNION ALL
  15. SELECT 1
  16. UNION ALL
  17. SELECT 2
  18. UNION ALL
  19. SELECT 3
  20. UNION ALL
  21. SELECT 4
  22. UNION ALL
  23. SELECT 5
  24. UNION ALL
  25. SELECT 6
  26. UNION ALL
  27. SELECT 7
  28. UNION ALL
  29. SELECT 8
  30. UNION ALL
  31. SELECT 9
  32. ;
  33.  
  34. INSERT INTO calendar_t
  35. ( `date`, DAY, MONTH, `year` )
  36. SELECT 
  37. date_add('2015-01-01', INTERVAL 100*a2.n + 10*a1.n + a0.n DAY) AS `date`,
  38. dayname(date_add('2015-01-01', INTERVAL 100*a2.n + 10*a1.n + a0.n DAY)) AS DAY,
  39. monthname(date_add('2015-01-01', INTERVAL 100*a2.n + 10*a1.n + a0.n DAY)) AS MONTH,
  40. YEAR(date_add('2015-01-01', INTERVAL 100*a2.n + 10*a1.n + a0.n DAY)) AS `year`
  41. FROM
  42. digits_v a2
  43. CROSS JOIN digits_v a1
  44. CROSS JOIN digits_v a0
  45. ORDER BY date_add('2015-01-01', INTERVAL 100*a2.n + 10*a1.n + a0.n DAY)
  46. ;

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.