Advertisement
Guest User

Untitled

a guest
Jun 18th, 2019
88
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.56 KB | None | 0 0
  1. declare @n int, @i int = 0
  2. select @n = month(getdate()) - 1
  3. select @n
  4.  
  5. if object_id('tempdb..#t12345') is not null drop table #t12345
  6.  
  7. create temp table #t12345 (
  8. id int identity(1,1) NOT NULL,
  9. check_date date)
  10.  
  11. while @i < 12
  12. begin
  13.  
  14. if (@n-@i) > 0
  15. begin
  16. insert into #t12345(check_date)
  17. select cast((@n - @i) as nvarchar(10)) +
  18. '/' +
  19. '01/' +
  20. cast(year(getdate()) as nvarchar(20))
  21. set @i = @i + 1
  22. end
  23. else
  24. begin
  25. insert into #t12345(check_date)
  26. select cast((12 - (@i - @n)) as nvarchar(10)) +
  27. '/' +
  28. '01/' +
  29. cast((year(getdate()) - 1) as nvarchar(20))
  30. set @i = @i + 1
  31. end
  32. end
  33.  
  34. CREATE TABLE my_date (the_date DATE);
  35.  
  36. INSERT INTO my_date VALUES ('2018-06-01');
  37.  
  38. SELECT generate_series((SELECT the_date FROM my_date) , NOW(), INTERVAL '1 MONTH')
  39. AS my_series;
  40.  
  41. my_series
  42. ----------------------
  43. 2018-06-01 00:00:00+01
  44. 2018-07-01 00:00:00+01
  45. 2018-08-01 00:00:00+01
  46. 2018-09-01 00:00:00+01
  47. 2018-10-01 00:00:00+01
  48. 2018-11-01 00:00:00+00
  49. 2018-12-01 00:00:00+00
  50. 2019-01-01 00:00:00+00
  51. 2019-02-01 00:00:00+00
  52. 2019-03-01 00:00:00+00
  53. 2019-04-01 00:00:00+01
  54. 2019-05-01 00:00:00+01
  55. 2019-06-01 00:00:00+01
  56.  
  57. SELECT generate_series('2018-06-01', NOW(), INTERVAL '1 MONTH');
  58.  
  59. the_date
  60. 2018-06-01
  61.  
  62. SELECT DATE(generate_series((SELECT the_date FROM my_date),
  63. NOW() - INTERVAL '1 MONTH', -- so you only get 12 results
  64. INTERVAL '1 MONTH'))
  65. AS my_series;
  66.  
  67. my_series
  68. ----------
  69. 2018-06-01
  70. 2018-07-01
  71. 2018-08-01
  72. 2018-09-01
  73. 2018-10-01
  74. 2018-11-01
  75. 2018-12-01
  76. 2019-01-01
  77. 2019-02-01
  78. 2019-03-01
  79. 10 rows of 12
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement