here is one hack
use sqlteam
go
create table sqlor(id int, date_imposter varchar(7))
;with src
as
(
select distinct top 10000 column_id as id, DATEADD(dd,column_id, GETDATE()) as date_imposter
from sys.all_columns
)
--2020-12-19 08:12:40.037
insert into sqlor
select id,
case when month(date_imposter) < 10 then
concat(cast(year(date_imposter) as varchar(4)), '/0' ,cast(month(date_imposter) as varchar(2)) )
else concat(cast(year(date_imposter) as varchar(4)), '/' ,cast(month(date_imposter) as varchar(2)) )
end
from src
select * from sqlor
go
create view vw_sqlor
as
select id,
date_imposter,
left(date_imposter, 4) as imposter_year_string,
substring(date_imposter, PATINDEX('%/%', date_imposter)+1, len(date_imposter) ) as imposter_month_string,
cast(left(date_imposter, 4) as int) as imposter_year_int,
cast(substring(date_imposter, PATINDEX('%/%', date_imposter)+1, len(date_imposter) ) as int) as imposter_month_int
from sqlor
go
select * from vw_sqlor
drop view vw_sqlor
drop table sqlor