0

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!

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
alexparq
  • 13
  • 5
  • 2
    What is the date type of column `SNAPSHOT_DAY`? – Wernfried Domscheit Jul 27 '16 at 10:09
  • `TO_DATE(dab.SNAPSHOT_DAY,'YYYYMMDD')` is totally senseless if `snapshot_day` is already a `date` column. –  Jul 27 '16 at 10:23
  • Rather than trying to fix this, you may be better off saying in plain English what the inputs are and what the desired result is. In any case comparing dates AFTER you transform them into strings is wrong, and it's not clear how you plan to subtract 1 from a string. –  Jul 27 '16 at 11:01
  • Thanks for the answers I have added more info to the question so that it is clearer to understand. As indicated in a below question, how could I do to verify the year as well? (i.e. January 2016 against December 2015) thanks! – alexparq Jul 27 '16 at 12:43
  • @alexparq - did it solve your question? – Gilad Green Jul 28 '16 at 09:43
  • It worked perfectly but I tried to finalize it by removing the days of the current week, arriving to this expression below dagm.SNAPSHOT_DAY BETWEEN TRUNC(ADD_MONTHS(TO_DATE('{RUN_DATE_DD/MM/YYYY}','DD/MM/YYYY'),-1),'WW') AND TRUNC(TO_DATE('RUN_DATE_DD/MM/YYYY','DD/MM/YYYY'),'WW') The thing is... it doesn't work. I have been reading on trunc statement and between, but I can't figure out why it is not working... – alexparq Jul 29 '16 at 07:35
  • ORA-01841: (full) year must be between -4713 and +9999, and not be 0 – alexparq Jul 29 '16 at 09:01
  • @alexparq - This question was solved so please consider on marking it as solved. For a new error you receive, if you can't solve it, open a separate question – Gilad Green Jul 29 '16 at 09:34

1 Answers1

0

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)
Gilad Green
  • 36,708
  • 7
  • 61
  • 95
  • That won't throw an ORA-01481 though; the MM string will be implicitly converted to a number. I suspect your solution is close anyway - comparing just the month number, without the year, probably isn't right. The -1 might need to be before the trunc - but it isn't clear what's really needed. – Alex Poole Jul 27 '16 at 10:21
  • I have provided further information above that will make my issue clearer Thanks a lot for your support and education! – alexparq Jul 27 '16 at 12:47
  • @alexparq - can you check now? – Gilad Green Jul 27 '16 at 13:06
  • hi Gilad It looks like it is working ( I will confirm it shortly ) but I have another question to complete what I want: If I want to make sure I get data for the whole previous month, would this modification work? dab.SNAPSHOT_DAY >= TRUNC(ADD_MONTHS(TO_DATE('{RUN_DATE_YYYYMMDD}','YYYYMMDD'),-1),'MM') – alexparq Jul 27 '16 at 13:42
  • @alexparq - for that other question check [here](http://stackoverflow.com/questions/1520789/how-can-i-select-the-first-day-of-a-month-in-sql) – Gilad Green Jul 27 '16 at 14:20