Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- declare @n int, @i int = 0
- select @n = month(getdate()) - 1
- select @n
- if object_id('tempdb..#t12345') is not null drop table #t12345
- create temp table #t12345 (
- id int identity(1,1) NOT NULL,
- check_date date)
- while @i < 12
- begin
- if (@n-@i) > 0
- begin
- insert into #t12345(check_date)
- select cast((@n - @i) as nvarchar(10)) +
- '/' +
- '01/' +
- cast(year(getdate()) as nvarchar(20))
- set @i = @i + 1
- end
- else
- begin
- insert into #t12345(check_date)
- select cast((12 - (@i - @n)) as nvarchar(10)) +
- '/' +
- '01/' +
- cast((year(getdate()) - 1) as nvarchar(20))
- set @i = @i + 1
- end
- end
- CREATE TABLE my_date (the_date DATE);
- INSERT INTO my_date VALUES ('2018-06-01');
- SELECT generate_series((SELECT the_date FROM my_date) , NOW(), INTERVAL '1 MONTH')
- AS my_series;
- my_series
- ----------------------
- 2018-06-01 00:00:00+01
- 2018-07-01 00:00:00+01
- 2018-08-01 00:00:00+01
- 2018-09-01 00:00:00+01
- 2018-10-01 00:00:00+01
- 2018-11-01 00:00:00+00
- 2018-12-01 00:00:00+00
- 2019-01-01 00:00:00+00
- 2019-02-01 00:00:00+00
- 2019-03-01 00:00:00+00
- 2019-04-01 00:00:00+01
- 2019-05-01 00:00:00+01
- 2019-06-01 00:00:00+01
- SELECT generate_series('2018-06-01', NOW(), INTERVAL '1 MONTH');
- the_date
- 2018-06-01
- SELECT DATE(generate_series((SELECT the_date FROM my_date),
- NOW() - INTERVAL '1 MONTH', -- so you only get 12 results
- INTERVAL '1 MONTH'))
- AS my_series;
- my_series
- ----------
- 2018-06-01
- 2018-07-01
- 2018-08-01
- 2018-09-01
- 2018-10-01
- 2018-11-01
- 2018-12-01
- 2019-01-01
- 2019-02-01
- 2019-03-01
- …
- 10 rows of 12
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement