CODEWITHSUNDEEP
sqloracle-databaseora-01481
alexparq
alexparq

Reputation: 13

SQL TO_CHAR TO_NUMBER TO_DATE

I have the following expression 

WHERE TO_CHAR(TO_DATE(dab.SNAPSHOT_DAY,'YYYYMMDD'),'MM') >=
      TO_CHAR(TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD'),'MM') -1

'{RUN_DATE_YYYYMMDD}' returns the date of when the data was captured.

I get the following error:

ORA-01481:invalid number format model

Columns are coherent in terms of data types. dab.SNAPSHOT_DAY is {DATE} 2016-07-24 00:00:00.0

Would you have any hint of what could be happening?

What I am trying to do is to get results from current and past month. That is why I want to transform both dates to MONTH (MM) and then substract 1. E.g.:

Snapshot_day = 05/JUN/2016 --> 06
RUN_DATE = 27/JUL/2016 --> 07
Comparing them: 06 >= 07 - 1 TRUE

Thank you all!

Upvotes: 0

Views: 2498

Answers (1)

Gilad Green
Gilad Green

Reputation: 37281

You can use trunc to get that specific part of the date that you want

where trunc(dab.SNAPSHOT_DAY,'MM') >= 
    trunc(to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD'),'MM') -1

But I think a better solution will be to:

where snapshot_day >= add_months(to_date('{RUN_DATE_YYYYMMDD}','YYYYMMDD'),-1)

And if the {RUN_DATE_YYYYMMDD} is actually a parameter from a type of date:

where snapshot_day >= add_months(run_date),-1)

Upvotes: 0

Related Questions