asked on

Mysql ifnull function

Hello,
I have two fields named nextbillingperiod (ex. 201311) and billingday (ex. 31), and I use the two fields to get my billingdate. Please, notes these are contracts are set up once and got billed every month just with one click; meaning that the field nextbillingperiod will be changing every month and the probability of having invalid date is higher as contracts can be billed at different dates within the same month (ex of invalid date 20130230).
To manage this, I wrote this mysql statement in my stored procedure.
Update contracts_billed set billingdate=ifnull(cast(concat(nextbillingperiod, billingday) as date), last_day(cast(concat(nextbillingperiod,'01') as date))).
Every time, it encounters an invalid date, instead of considering the second part of ifnull function , the system pops an error message stating invalid date.
Is there something wrong with the statement?
I am looking forward to hearing from you.

SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Omer-Pitou

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Stanyon

Yeah - if you're already half way through a project, changing the data structure can be a bit of a nightmare - emphasises the importance of good database design BEFORE you start coding - but hindsight is a wonderful thing.

Avoids the kind of Hacks you're now needing to implement - live and learn eh!!

Good luck with it - pleased you got it working :)

Omer-Pitou

ASKER

Thanks Chris

Omer-Pitou

ASKER

I managed to avoid the invalid date error message through a stored function