Solved

Mysql ifnull function

Posted on 2013-11-22
5
410 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 500 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

705 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