I have months in the following format -

2022  /  12
2021  /  03
2020  /  02

YYYY  /  MM

I want to find out the last day of these months. i.e. 2022/12/31, 2021/03/31 , 2020/02/28

I am using the below code -

to_date(to_char(:p_month||'01/', 'yyyy / mm/dd'), 'yyyymmdd')

but it is throwing an invalid format error.

CodePudding user response：

There are following steps you need to take in order to get your desired result:

1. Remove the whitespaces within your string
2. Cast your string to a date
3. Select the last day of the month for this date.

There is likely more than one way to do this. As example you can do following:

1st part: SELECT REPLACE(datepart,' ','') FROM yourtable; to remove whitespaces...

2nd part: SELECT TO_DATE(REPLACE(datepart,' ',''),'YYYY/MM') FROM yourtable; to cast your column as date and...

3rd part: SELECT LAST_DAY (TO_DATE(REPLACE(datepart,' ',''),'YYYY/MM')) FROM yourtable; to get the last day of the month for this date.

Please see the working example here: db<>fiddle

CodePudding user response：

First you need to convert the input string to a date - so the first function you apply should be to_date (you have the two conversions in the wrong order in your attempt). Oracle is tolerant of unneeded spaces in the input string; and to_date accepts incomplete formats, supplying defaults for the missing components.

So, to_date(:p_month, 'yyyy/mm') will return the first day of the month (that's the default: dd = 1, as well as hh24:mi:ss = 00:00:00).

Then you can apply last_day to this result, and if needed, convert back to a string using to_char. Like this:

    to_char(last_day(to_date(:p_month, 'yyyy/mm')), 'yyyymmdd')

Testing it:

variable p_month varchar2(10)
exec :p_month := '2022 / 03'

select :p_month as mth,
       to_char(last_day(to_date(:p_month, 'yyyy/mm')), 'yyyymmdd') as last_day
from   dual;

MTH        LAST_DAY
---------- --------
2022 / 03  20220331

By the way, the specific error you got was because you put the concatenated slash in the wrong place. You concatenated 01/ instead of /01, so your string became 2022 / 1201/ instead of 2022 / 12/01. Oracle tolerates unnecessary spaces, but it can't work with delimiters in the wrong place.