-1

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.

Qirel
  • 25,449
  • 7
  • 45
  • 62
Ken
  • 63
  • 13
  • Your query is invalid. `FROM CLIENT_TABLE AND DATEPART....`, you probably mean a `WHERE`? – Qirel Jun 26 '19 at 15:26
  • 3
    Those functions are not valid [MySQL functions](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html). Those are SQL Server/T-SQL functions. ([DATEADD](https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-2017), [DATEPART](https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017)) – aynber Jun 26 '19 at 15:26
  • What is your underlying database? Is it SQL Server or MySQL? – Tim Biegeleisen Jun 26 '19 at 15:28
  • @TimBiegeleisen "*Currently using MySQL 5.6.44.*", so MySQL. Which, as anyber said, is different that SQL Server. ;-) – Qirel Jun 26 '19 at 15:29
  • Thank you all, I had to delete a bit of personal info in my code in order to post and may have missed some syntax. So SQL !=MySQL? Still new to this, I appreciate the quick feedback – Ken Jun 26 '19 at 15:42
  • Each database type has their own functions and quirks. You need to make sure you're looking at the correct documentation and tutorials/scripts for your database type. – aynber Jun 26 '19 at 15:46

1 Answers1

2

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);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360