0

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.

SSA_Tech124
  • 577
  • 1
  • 9
  • 25
  • 30 days have September, April. June. and November. All the rest have 31 days excepting February which is 28 days long and 29 days in a leap year. You could probably use that as part of the solution. Another way would be to take the first day of the month following as a date and substract one day. – Keith John Hutchison May 08 '22 at 06:16
  • 2
    Why should he do such crazy things instead of just using the LAST_DAY function? – Jonas Metzler May 08 '22 at 06:24
  • I think you have `to_date` and `to_char` switched around. Then as @JonasMetzler says, `last_day` gives you the last day of the month that a date falls in, so you don't need any `dd`. – William Robertson May 08 '22 at 08:06

2 Answers2

1

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

Jonas Metzler
  • 4,517
  • 1
  • 5
  • 17
0

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.