0

Please, can someone help me with this part of query? It's working fine in Microsoft Access but won't work in MySQL:

DateDiff("m",[first_sent_date],[last_sent_date])+1 AS basetime_month, Count(tablename.id) AS cnt_all
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
LeBro
  • 1
  • 2
  • 1
    You've got to translate your query to the sql dialect of MySQL. MySQL uses i.e. backticks to quote column names, functions are different too. The [MySQL manual](https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html) will help you. – VMai May 12 '14 at 11:34

1 Answers1

1

The Access SQL DateDiff function takes at least three (3) arguments. It returns different values based on the first argument, which is the "interval". In your case

DateDiff("m",[first_sent_date],[last_sent_date])

the "m" indicates that you want the difference in months.

The MySQL DATEDIFF function only takes two (2) arguments and always returns the difference in days. The MySQL equivalent to the Access SQL DateDiff expression above would be something like

((YEAR(`last_sent_date`) - YEAR(`first_sent_date`)) * 12) + MONTH(`last_sent_date`) - MONTH(`first_sent_date`)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418