Answers.Body: <p>My friendly neighborhood Civi expert solved this problem.
  The issue is with the "repeat" option in the scheduled reminder form.</p>
  
  <p>If you specify, when to repeat without the "Until" piece, it generates faulty SQL. 
  Our expert modified the form so you can't leave that value blank if you click the repeat radio box.</p>
  
  <p><a href="https://i.sstatic.net/oeYcU.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/oeYcU.png" alt="enter image description here"></a></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-06T17:03:21.390Z
Answers.Text: A patch was submitted upstream for this problem yesterday - presumably Brian S. is your CiviCRM person!  For anyone else with this problem, that patch is at [CRM-21523](https://issues.civicrm.org/jira/browse/CRM-21523).
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-03-16T09:19:56.697Z
Answers.Text: Small comment on this: We just had the problem that neither a reminder date not hours before event was filled out, thus creating a faulty SQL statement matching your problem as well.
  
  While I think that this is not smart execution on the user site, but the UI shouldn't allow to create a faulty reminder either.
Answers.UserDisplayName: Phil_B
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-13T18:53:07.960Z
Answers.Text: I am still getting same error in civicrm 5.6.1 and Drupal 7.63. 
  Repeat option is selected and 'Until' field is populated
Answers.UserDisplayName: Hassan Farooq
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-06T16:00:12.977Z
Answers.LastActivityDate: 2017-12-06T16:00:12.977Z
Answers.UserDisplayName: mr. mike
Body: <p>CiviCRM 4.7.27
  Usually, our CiviCRM guru handles these, but I figured I would ask a wider audience. </p>
  
  <p>When choosing date instead of an interval, the scheduled reminder fails with a DB syntax error. The error is in the " DATE_SUB(, INTERVAL hour))" part of the SQL. </p>
  
  <p>The first argument to the function is missing and the time component for the interval is missing: (see below in bold). I would imagine this is a bug.. The SQL from the logfile:</p>
  
  <p>Here's the SQL that is created by CiviCRM and fails:</p>
  
  <p>SELECT e.contact_id as contact_id, e.id as entity_id, "civicrm_membership" as entity_table, 3 as action_schedule_id, MAX as latest_log_time
  FROM civicrm_membership e
  INNER JOIN civicrm_contact c ON c.id = e.contact_id AND c.is_deleted = 0 AND c.is_deceased = 0
  INNER JOIN civicrm_action_log reminder ON reminder.contact_id = e.contact_id AND
  reminder.entity_id = e.id AND
  reminder.entity_table = 'civicrm_membership' AND
  reminder.action_schedule_id = 3
  WHERE (e.membership_type_id IN ("21")) AND (( e.is_override IS NULL OR e.is_override = 0 )) AND (e.status_id IN (5, 2, 9, 10, 4, 8)) 
  AND ("20171108110009" &lt;= <strong>DATE_SUB(, INTERVAL hour))</strong>
  GROUP BY reminder.contact_id, reminder.entity_id, reminder.entity_table
  HAVING (TIMESTAMPDIFF) >= TIMESTAMPDIFF))</p>
  
  <p><strong>NOTE</strong>: There are missing parameters in the DATE_SUB function, the SQL fails and the scheduled reminder does not go out.</p>
  
Tags: <scheduled-reminders>
Title: Scheduled Reminders generates faulty SQL
ThreadText: # Scheduled Reminders generates faulty SQL
  
  (Asked by: username_0 on 2017-11-17)
  
  CiviCRM 4.7.27
  Usually, our CiviCRM guru handles these, but I figured I would ask a wider audience.
  
  When choosing date instead of an interval, the scheduled reminder fails with a DB syntax error. The error is in the " DATE\_SUB(, INTERVAL hour))" part of the SQL.
  
  The first argument to the function is missing and the time component for the interval is missing: (see below in bold). I would imagine this is a bug.. The SQL from the logfile:
  
  Here's the SQL that is created by CiviCRM and fails:
  
  SELECT e.contact\_id as contact\_id, e.id as entity\_id, "civicrm\_membership" as entity\_table, 3 as action\_schedule\_id, MAX as latest\_log\_time
  FROM civicrm\_membership e
  INNER JOIN civicrm\_contact c ON c.id = e.contact\_id AND c.is\_deleted = 0 AND c.is\_deceased = 0
  INNER JOIN civicrm\_action\_log reminder ON reminder.contact\_id = e.contact\_id AND
  reminder.entity\_id = e.id AND
  reminder.entity\_table = 'civicrm\_membership' AND
  reminder.action\_schedule\_id = 3
  WHERE (e.membership\_type\_id IN ("21")) AND (( e.is\_override IS NULL OR e.is\_override = 0 )) AND (e.status\_id IN (5, 2, 9, 10, 4, 8))
  AND ("20171108110009" <= **DATE\_SUB(, INTERVAL hour))**
  GROUP BY reminder.contact\_id, reminder.entity\_id, reminder.entity\_table
  HAVING (TIMESTAMPDIFF) >= TIMESTAMPDIFF))
  
  **NOTE**: There are missing parameters in the DATE\_SUB function, the SQL fails and the scheduled reminder does not go out.
  
  **Tags:** scheduled-reminders
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2017-12-06. Score: 3 | Accepted answer
  
  My friendly neighborhood Civi expert solved this problem.
  The issue is with the "repeat" option in the scheduled reminder form.
  
  If you specify, when to repeat without the "Until" piece, it generates faulty SQL.
  Our expert modified the form so you can't leave that value blank if you click the repeat radio box.
  
  [![enter image description here](https://i.sstatic.net/oeYcU.png)](https://i.sstatic.net/oeYcU.png)
  
  > **Comments:**
  > **username_1**: A patch was submitted upstream for this problem yesterday - presumably Brian S. is your CiviCRM person!  For anyone else with this problem, that patch is at [CRM-21523](https://issues.civicrm.org/jira/browse/CRM-21523).  
  > **username_2**: Small comment on this: We just had the problem that neither a reminder date not hours before event was filled out, thus creating a faulty SQL statement matching your problem as well.  While I think that this is not smart execution on the user site, but the UI shouldn't allow to create a faulty reminder either.  
  > **username_3**: I am still getting same error in civicrm 5.6.1 and Drupal 7.63.  Repeat option is selected and 'Until' field is populated  
  
  ---
  
  