I saw that this site is very helpful and i wonder if someone with more experience can help me.
I have this column "Calendar Month" as nvarchar. Every time i am doing the refresh the data changes, so this is just a sample.
Calendar Month
201711
201712
201801
201802
201803
201804
201805
201806
201807
201808
201809
201810
201811
201812
201901
201902
201903
201904
201905
201911
201912
202011
202001
I wonder how i can extract only the data between the first calendar date and the refresh date.
The refresh date is the date in which i make the refresh.
Declare @refreshdate date
Set @refreshdate = Getdate()
My problem is that the calendar date starts in november not january. So for me , the year started on 1 November 2018. and now i am on 21 May 2019. So from my list i would only need to extract:
201811
201812
201901
201902
201903
201904
201905
Another example: the refresh date is 1st of November 2019. I would only need to extract 201911
or if the refresh date is December 2020 i would only need 202011 and 202012
in order to do comparison I had to convert your 'date' fields into int.
Basically you are using a yyyymm as date which is not a good design I would recommend. Because of this flaw I had to convert thing and cast things. This shows a flaw in your data types. I would recommend you reconsider this design.
Declare @refreshdate nvarchar(6) , @firstOfYear int = '201811'
--Set @refreshdate = Getdate()
select @refreshdate = convert(nvarchar(6) , Getdate(), 112)
select @refreshdate
---sample data for us to use
create table #onus(CalendarMonth nvarchar(10))
insert into #onus
select '201711' as CalendarMonth union
select '201712' union
select '201801' union
select '201802' union
select '201803' union
select '201804' union
select '201805' union
select '201806' union
select '201807' union
select '201808' union
select '201809' union
select '201810' union
select '201811' union
select '201812' union
select '201901' union
select '201902' union
select '201903' union
select '201904' union
select '201905' union
select '201911' union
select '201912' union
select '202011' union
select '202001'
select * From #onus
--this part is for you to use in your where clause
where cast(CalendarMonth as int)
between @firstOfYear and cast(@refreshdate as int)
--this is sample data
drop table #onus
