Code:
DATEDIFF(expr1,expr2)
DATEDIFF() returns expr1 – expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
-> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
-> -31
Potpuno ti je nepotreban deo sa <> NULL (vec ti je receno da za to moras da koristis IS [NOT] NULL )
pogledaj:
http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html vezano za kako se radi sa NULL vrednosti
.. tako da bi tvoj upit izgledao:
Code:
DATEDIFF(tabela1.enddate,tabela1.startdate) AS razlikadana
primer
Code:
mysql> SELECT DATEDIFF('2010-11-20', '2010-11-10')\G
*************************** 1. row ***************************
DATEDIFF('2010-11-20', '2010-11-10'): 10
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF('2010-11-20', NULL)\G
*************************** 1. row ***************************
DATEDIFF('2010-11-20', NULL): NULL
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF(NULL, '2010-11-10')\G
*************************** 1. row ***************************
DATEDIFF(NULL, '2010-11-10'): NULL
1 row in set (0.00 sec)
mysql> SELECT DATEDIFF(NULL, NULL)\G
*************************** 1. row ***************************
DATEDIFF(NULL, NULL): NULL
1 row in set (0.00 sec)
dakle ako je bilo koji parametar DATEDIFF funkcije NULL, vratice NULL. onda u aplikaciji taj NULL prikazi kako treba posto razlika od 0 dana nije ista kao "pogresno unet podatak" i ako tretiras u aplikaciji te dve stvari identicno - negde gadno gresis.
Ako bas oces da ti to bude "isto" onda mozes:
Code:
mysql> create table t1 (startdate date, enddate date);
mysql> insert into t1 values ('2010-11-10', '2010-11-20');
mysql> insert into t1 values ('2010-11-10', NULL);
mysql> SELECT IF(t1.enddate IS NOT NULL AND t1.startdate IS NOT NULL, DATEDIFF(t1.enddate, t1.startdate), 0) razlika FROM t1;
+---------+
| razlika |
+---------+
| 10 |
| 0 |
+---------+