CODEWITHSUNDEEP
sqloracle-database
xcelm
xcelm

Reputation: 561

ORACLE subtraction from user defined variable

what I'm trying to accomplish is to subtract 1 month from user given date variable. My code in where conditions is as following:

CONDITION >= to_char(add_months('&datum_od', 1), 'dd.mm.yyyy')

I am getting "ORA-1843: not a valid month" error.

Could you guys please help me out here?

Upvotes: 0

Views: 331

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

to_date(CONDITION,'dd.mm.yyyy') >= add_months(to_date('&datum_od','dd.mm.yyyy'), -1)

Please notice

  • If the dates are stored as strings (why?) the format should contain YYYY, MM and DD in that order ,e.g. 'YYYY-MM-DD', 'YYYYMMDD' etc.
  • for subtraction you have to use -1

Upvotes: 1

Kacper
Kacper

Reputation: 4818

Error suggest that you ave incorrect format of date. Probably caused by &datum_od is in other format than NLS date.

Try:

CONDITION >= to_char(add_months(to_date(&datum_od,'yyyy-mm-dd'), 1), 'dd.mm.yyyy')

And pass your variable &datum_od in format specified.

Next thing is why do you do >= comparison on strings not on dates? To_char seems wrong here.

Upvotes: 1

Related Questions