From 1821b8a6299733585a7a196bc8cb82c007206737 Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Tue, 1 Nov 2022 15:08:30 -0400
Subject: [PATCH] APIv4 - Add tests for SQL DATE functions

---
 .../phpunit/api/v4/Action/SqlFunctionTest.php | 30 +++++++++++++++++++
 1 file changed, 30 insertions(+)

diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
index 773bb67469..1cbb7384ff 100644
--- a/tests/phpunit/api/v4/Action/SqlFunctionTest.php
+++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
@@ -214,6 +214,36 @@ class SqlFunctionTest extends Api4TestBase implements TransactionalInterface {
     $this->assertEquals('q', $result['LOWER:middle_name']);
   }
 
+  public function testDateFunctions() {
+    $lastName = uniqid(__FUNCTION__);
+    $sampleData = [
+      ['first_name' => 'abc', 'last_name' => $lastName, 'birth_date' => '2009-11-11'],
+      ['first_name' => 'def', 'last_name' => $lastName, 'birth_date' => '2010-01-01'],
+    ];
+    $contacts = $this->saveTestRecords('Contact', [
+      'records' => $sampleData,
+    ]);
+
+    $result = Contact::get(FALSE)
+      ->addSelect('DATEDIFF("2010-01-01", birth_date) AS diff')
+      ->addSelect('YEAR(birth_date) AS year')
+      ->addSelect('MONTH(birth_date) AS month')
+      ->addSelect('EXTRACT(YEAR_MONTH FROM birth_date) AS year_month')
+      ->addWhere('last_name', '=', $lastName)
+      ->addOrderBy('id')
+      ->execute();
+
+    $this->assertEquals(51, $result[0]['diff']);
+    $this->assertEquals(2009, $result[0]['year']);
+    $this->assertEquals(11, $result[0]['month']);
+    $this->assertEquals('200911', $result[0]['year_month']);
+
+    $this->assertEquals(0, $result[1]['diff']);
+    $this->assertEquals(2010, $result[1]['year']);
+    $this->assertEquals(1, $result[1]['month']);
+    $this->assertEquals('201001', $result[1]['year_month']);
+  }
+
   public function testIncorrectNumberOfArguments() {
     try {
       Activity::get(FALSE)
-- 
2.25.1