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.
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:
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
i would apreciate any idea. Thank you!
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)
---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 * 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