Solved

Mysql ifnull function

Posted on 2013-11-22
5
389 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:Omer-Pitou
  • 3
  • 2
5 Comments
 
LVL 42

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 500 total points
Comment Utility
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
 

Accepted Solution

by:
Omer-Pitou earned 0 total points
Comment Utility
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
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
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
 

Author Comment

by:Omer-Pitou
Comment Utility
Thanks Chris
0
 

Author Closing Comment

by:Omer-Pitou
Comment Utility
I managed to avoid the invalid date error message through a stored function
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now