Home > OS >  I need date in specified format
I need date in specified format

Time:09-23

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;
  • Related