?
Solved

Mysql ifnull function

Posted on 2013-11-22
5
Medium Priority
?
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 43

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 2000 total points
ID: 39671331
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
ID: 39671584
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 43

Expert Comment

by:Chris Stanyon
ID: 39671608
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
ID: 39671611
Thanks Chris
0
 

Author Closing Comment

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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

777 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