SHARE
TWEET

Untitled

a guest Jun 18th, 2019 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top