Search code examples
phpsqldateadddatepart

SQL statement with 'DATEPART' and 'DATEADD' do not work. Are not recognized by server

Trying to pull client information from SQL server using PHP and count how many clients signed up last month based on the date they signed up. I want to use DATEPART and DATEADD to specify the parameters I want for grabbing last months information, but the server doesn't seem to recognize these parameters. Currently using MySQL 5.6.44.

I know statements like 

SELECT CLIENT_EMAIL 
FROM CLIENT_TABLE 
AND DATE_CLIENT_ADDED > (CURRENT_DATE() - INTERVAL 30 DAY)

works, but this is very limiting

$con = mysqli_connect($Host, $User, $Password, $DB);

$getLastMonthEnrollments = "SELECT CLIENT_EMAIL FROM CLIENT_TABLE AND DATEPART(m, DATE_CLIENT_ADDED) = DATEPART(m, DATEADD(m, -1, getdate()))";
$tempQuery = mysqli_query($con, $getLastMonthEnrollments);
$LastMonthEnrollments = mysqli_num_rows($tempQuery);

I expect to get a number from counting the rows, but my result is null. When I attempt to run this within the server itself, I get

FUNCTION my_DB.DATEPART does not exist

Am I doing this wrong? I've read many documentations and questions on here and this seems to be the correct usage.

Solution

  • You are using SQL Server syntax from your Laravel code, yet the underlying database is MySQL. Here is your query, rewritten and also corrected:

    SELECT CLIENT_EMAIL
FROM CLIENT_TABLE
WHERE MONTH(DATE_CLIENT_ADDED) = MONTH(CURDATE() - INTERVAL 1 MONTH);