Solved

Mysql ifnull function

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MYSQL simple update statement 3 54
sql_mode 1 24
Excel - SQL export question 3 42
INDEX does not make a difference, why? 10 51
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

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