10

I have this problem if anyone can help. There is a field (date) in my table (table1) that is a date in the format 3/31/1988 (M/D/y), and my necessity is to define how many days have passed since that date.

I have tried to give this instruction

SELECT DATEDIFF(CURDATE(), date) AS days
FROM table1

But it gives back 'null' and I think this happens because the two date formats are different (CURDATE() is YMD.....

Is it correct? can anyone help me? Thank you in advance

John
  • 1
  • 13
  • 98
  • 177
user1951561
  • 119
  • 1
  • 1
  • 9

4 Answers4

15

You can use STR_TO_DATE():

SELECT DATEDIFF(CURDATE(),STR_TO_DATE(date, '%m/%d/%Y')) AS days
FROM table1

SQLFiddle Demo

John
  • 1
  • 13
  • 98
  • 177
DarkAjax
  • 15,955
  • 11
  • 53
  • 65
  • CURDATE() return date format like Y-m-d. So if both date format is not match then the DATEDIFF function return NULL. Hence date format should be same like below: SELECT DATEDIFF(CURDATE(),STR_TO_DATE(date, '%Y-%m-%d')) AS days FROM table1 – Joy Chowdhury Jun 03 '18 at 18:45
2

Your DATE field should have DATE or DATETIME format to be used as DATEDIFF argument correctly.

Also DATE is MySQL keyword and I am not sure that you can use it as valid field name.

zavg
  • 10,351
  • 4
  • 44
  • 67
2

You can use this for accurate result

SELECT DATEDIFF(CURDATE(), DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(`date`)), '%Y-%m-%d')) AS days FROM `table1`
Joy Chowdhury
  • 153
  • 1
  • 11
0

If you want to consider results without - signs that you have to follow parameters position as below :

SELECT DATEDIFF(Big_Date,Small_Date) AS days FROM table1.

positive results e.g 5 (with no sign), if you place a Small date as the first parameter then it will results minus sign e.g -5.

cosmoonot
  • 2,161
  • 3
  • 32
  • 38
Waiseman
  • 31
  • 3