2

I'm, trying to calculate the number of days between two dates using ANSI SQL standard. But I'm missing something as this statement returns NULL in MySQL.

SELECT EXTRACT(DAY FROM DATE('2009-01-25') - DATE('2009-01-01')) AS day_diff;

I'm aware of the MySQL DATEDIFF function, but I'm curious why this code isn't working.

What am I missing?

Thomas Dickey
  • 51,086
  • 7
  • 70
  • 105
Andre
  • 1,347
  • 3
  • 14
  • 23
  • If you `extract DAY` from a date, you lose the month and year. So this does not look like a valid way to achieve what you want. Perhaps the simple `DATE('2009-01-25') - DATE('2009-01-01')` is ANSI SQL, not sure though. – ypercubeᵀᴹ Nov 25 '13 at 17:23

1 Answers1

2

Is this what you meant to do?

mysql> SELECT EXTRACT(DAY FROM DATE('2009-01-25')) - 
         EXTRACT(DAY FROM DATE('2009-01-01')) AS day_diff;
+----------+
| day_diff |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)

UPDATE:

If you want this to work for dates in different months (or even different years), then you can use the MySQL DATEDIFF() function.

Examples:

mysql> select datediff('2009-04-25','2009-01-01');
+-------------------------------------+
| datediff('2009-04-25','2009-01-01') |
+-------------------------------------+
|                                 114 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> select datediff('2010-04-25','2009-01-01');
+-------------------------------------+
| datediff('2010-04-25','2009-01-01') |
+-------------------------------------+
|                                 479 |
+-------------------------------------+
1 row in set (0.00 sec)
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • This seems to fail when the months are different. `SELECT EXTRACT(DAY FROM DATE('2009-04-25')) - EXTRACT(DAY FROM DATE('2009-01-01')) AS day_diff;` – Andre Jan 04 '13 at 21:49
  • I mentioned it in my initial post, but I'm aware of DATEDIFF. I was wondering why the statement I posted wasn't working as I thought it were the ANSI 92 standard for accomplishing what I'm trying to do. – Andre Jan 05 '13 at 13:13