2

I'm trying to calculate the difference between two date columns, which works just fine as:

select (ship_date - due_date) AS DaysTaken;

Basically, I want to determine if orders were shipped on time and, if not, how many days late. This works fine, as expected, except, if the order is shipped early, I get a negative number. What I'd like to do is to evaluate the results and return 0, if it calculates a negative value.

I don't know how to do this in a MySQL select statement. Can anyone help?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Merrill
  • 21
  • 5
  • Near duplicate of [How to get the max of two values in MySQL?](http://stackoverflow.com/questions/1565688/). – outis Dec 09 '11 at 09:05

2 Answers2

2

You can use the GREATEST() function to replace negative numbers with 0:

select GREATEST((ship_date - due_date),0) AS DaysTaken;
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • @Merrill: if an answer resolves your question, [accept](http://meta.stackexchange.com/questions/5234/) it. If it's useful but doesn't resolve your question (or does resolve the question, but there's another answer that is better), upvote the question. – outis Dec 09 '11 at 09:07
1
SELECT  GREATEST(ship_date - due_date, 0) AS DaysTaken
Quassnoi
  • 413,100
  • 91
  • 616
  • 614