e.g. In below table
Formonth | ForYear
Mar | 2020
May | 2021[enter image description here][1]
Jun | 2017
So My Expected result in third column should be MyThirdColumnValue
2021-03-31
2021-05-31
2017-06-30
CodePudding user response:
If you are using sql server it's very simple. You can use EOMONTH()
function.
Schema and insert statements:
create table testtable(ForMonth varchar(10), ForYear int);
insert into testtable values('Mar', 2020);
insert into testtable values('May', 2021);
insert into testtable values('Jun', 2017);
Output:
select ForMonth,ForYear, EOMONTH(ForMonth ' 1 ' cast(ForYear as varchar)) as MyThirdColumnValue
from testtable
Output:
ForMonth | ForYear | MyThirdColumnValue |
---|---|---|
Mar | 2020 | 2020-03-31 |
May | 2021 | 2021-05-31 |
Jun | 2017 | 2017-06-30 |
db<>fiddle here
CodePudding user response:
Sqlserver can be implemented like this:
create table and insert data:
create table ##test(year varchar(10),month varchar(10));
insert into ##test values('2021','Mar');
insert into ##test values('2020','May');
insert into ##test values('2017','Jun');
Data processing and query:
select month,year,
case when month = 'Mar' then dateadd(dd,-1,cast(concat(year,'0401') as date))
when month = 'May' then dateadd(dd,-1,cast(concat(year,'0601') as date))
when month = 'Jun' then dateadd(dd,-1,cast(concat(year,'0701') as date))
end as thevalue
from ##test;