MySQL Query Help - DATEDIFF and Writing Results to A New Column

Question

Ok so I have an obnoxious problem to solve here and im stumped.

I have a MySQL table with basically this structure

id  transdate  startdate   enddate      unit1  unit1price   unit2   unit2price

1   2014-07-31   2014-10-25  2014-10-29   22     25.00        27      25.00

What I need to do is create another column that is totaldays which calculates the number of days between startdate and enddate.

How do I do this?

Thanks so much

If i put `get date difference in MySQL` in Google i get a zillion counts,what have you tried? — Mihai, Oct 10 '14 at 18:58
http://stackoverflow.com/questions/4759248/difference-between-two-dates-in-mysql — Sherman, Oct 10 '14 at 18:58
I get that I need to use DATEDIFF but how do I loop through the rows and write that value to a new column? — alexboorman, Oct 10 '14 at 19:02

score 0 · Answer 1 · answered Oct 10 '14 at 18:58

0

You may try something like:

SELECT datediff(enddate, startdate)...

More info here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff

answered Oct 10 '14 at 18:58

MillaresRoo

3,808
1
31
37

score 0 · Accepted Answer · answered Oct 10 '14 at 19:16

0

--create the column

alter table YourTableName
add column daysdiff int;

--update the new column with appropriate computation

update YourTableName
set daysdiff = datediff(enddate, startdate);

answered Oct 10 '14 at 19:16

Eric

4,201
5
27
36

MySQL Query Help - DATEDIFF and Writing Results to A New Column

2 Answers2