Sean Davis Sean Davis - 1 month ago 4
MySQL Question

MySQL - Can't Convert From MSSQL

Trying To convert This MSSQL 2012R2 Statement into MySQL 5.7

SELECT SUM(Cost + Credit_Amount) As Daily_Cost FROM Google_Costing
WHERE Date BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE() -2), 0) AND
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE() -2) + 1, 0)


I have tried replacing
DATEADD
with
DATE_ADD
and
DATEDIFF
with
TIMESTAMPDIFF
, but still no luck. Any help is appreciated.

I get the error:


Incorrect parameter count in the call to native function 'DATEDIFF'


So I change the statement to:

SELECT SUM(Cost + Credit_Amount) As Daily_Cost FROM Google_Costing
WHERE Date BETWEEN DATE_ADD(MONTH, TIMESTAMPDIFF(MONTH, 0, GETDATE() -2), 0) AND
DATE_ADD(MONTH, TIMESTAMPDIFF(MONTH, 0, GETDATE() -2) + 1, 0)


This time I get the error:


You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use
near 'TIMESTAMPDIFF(MONTH, 0, GETDATE() -2), 0) AND DATE_ADD(MONTH,
TIMESTAMPDIFF(MONT' at line 2

Answer

Try out these biscuits...

SELECT SUM(Cost + Credit_Amount) As Daily_Cost FROM Google_Costing
WHERE Date BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') 
AND DATE_ADD(CURDATE(), INTERVAL -2 DAY)
Comments