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.
Omer-PitouAsked:
Who is Participating?
 
Omer-PitouConnect With a Mentor Author Commented:
Hello Chris,
This is a valid point and I tested it it works with date_add(previousbillingdate, interval 1 month). This logic will force me to save the previousbillingdate per contract billed every time I close a month. This means a Change to the database structure and number of stored procedures.
The alternative I used now to avoid database structure changes and user forms changes due to time constraint is to create a stored function and use that in the stored procedures. There many other constraints.
The content of stored function is
Set @p_billingdate=ifnull(cast(concat(p_billingperiod,p_billingday) as date), last_day(cast(concat(p_billingperiod,'01') as date)));
Return @p_billingdate;
And the stored procedure,
Update contracts_billed set billingdate=function_billingdate(nextbillingperiod, billingday).

As this worked, I can consider reporting the previous update statement posted as a bug.
0
 
Chris StanyonConnect With a Mentor Commented:
There's nothing wrong with your statement - it's just the way it works. When you try and cast a string into an invalid date, it will throw an error.

Thinking slightly differently here, you seem to storing 3 separate bits of info for 1 purpose - the billing date.

Why not just store the next billing date and work with that. What purpose does the billingperiod and billingday have that you couldn't use the billing date for?
0
 
Chris StanyonCommented:
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 :)
0
 
Omer-PitouAuthor Commented:
Thanks Chris
0
 
Omer-PitouAuthor Commented:
I managed to avoid the invalid date error message through a stored function
0
All Courses

From novice to tech pro — start learning today.