Start and end of a month / year passed in parameter (Oracle)

1

I need to return the total monthly data according to the parameter of MONTH and YEAR reported.

I did as follows:

WHERE a.dt_mesano_referencia between 
to_date('01/'|| :NR_MES ||'/'|| :NR_ANO ||' 00:00:00', 'dd/mm/yyyy hh24:mi:ss') and
last_day(to_date( :NR_MES ||'/'|| :NR_ANO ||' 23:59:59', 'mm/yyyy hh24:mi:ss'))

But when I run, I get the following error return:

Is there another way to do it?

    
asked by anonymous 14.06.2018 / 15:08

1 answer

1

Instead of viewing start and end date and time, let's consider the month and year in the search only.

For this we need to truncate the date to not bring the time, then change the time format with to_char , to only bring the month and year, and then compare with the parameters passed:

 WHERE TO_CHAR(TRUNC(a.dt_mesano_referencia)
              ,'mm/yyyy') = '&NR_MES' || '/' || '&NR_ANO'
    
14.06.2018 / 16:54