Answers

• nlooije Posts: 44Questions: 8Answers: 0

  October 2021 edited October 2021

  It seems that in datatables.js it is forced to convert the datetime toISO when using moment. I think there should be an option to give the format how to push back the input of the datetime format and how the display it on the screen.

  When I change the code in datatables.js to not make an toISOString it works with the searchBuilderType: 'moment-YYYYMMDD':

  else if (this.s.type.includes('moment')) {
                      for (var i = 0; i < this.s.value.length; i++) {
                          // this.s.value[i] = moment(this.s.value[i], this.s.dateFormat).toISOString();
                          this.s.value[i] = this.s.value[i];
                      }
                  }
  

  Then the $_POST is:

  searchBuilder[criteria][0][condition]: =
  searchBuilder[criteria][0][data]: Date confirmed
  searchBuilder[criteria][0][origData]: BEVESTIGDELEVERDATUM
  searchBuilder[criteria][0][type]: moment-YYYYMMDD
  searchBuilder[criteria][0][value][]: 20211027
  searchBuilder[criteria][0][value1]: 20211027
  searchBuilder[logic]: AND
  

  This should also be possible for searchBuilderType: 'date', just give some extra information in which form push the date back to the query
• sandy Posts: 913Questions: 0Answers: 236

  October 2021

  Hi @nlooije ,

  The line that you are referencing isn't just used for server side processing, but also when using client side processing, allowing easy comparison of dates.

  Is that column a date type in your database? Or is it stored as a string?

  Since you have access to the POST data within the controller you could spin through the data there and convert it to whatever format you wish.

  Thanks,
  Sandy
• nlooije Posts: 44Questions: 8Answers: 0

  October 2021

  The column in the database is an string in the form of YYYYMMDD. With the editor it works perfectly in combination with the DataTables PHP library. When using the editor it also send by $_POST an edit for the date in the form of YYYY-DD-MM and then the PHP with setFormatter uses in the queries the YYYYMMDD format. For the searchbuilder it it doens't use the setFormatter and just put in straight the values of the search boxes into the SQL queries. The $_POST I can't change because it is injected directly into the PHP library with ->process($_POST)
• nlooije Posts: 44Questions: 8Answers: 0

  November 2021

  I have solved it now to manipulate the $_POST data with the following:

  // Convert date YYY-MM-DD to YYYYMMDD in for Searchbuilder
  if (!empty($_POST)) {
      // Array of post columns to manipulate.
      $postNameArr = array('DATE1', 'DATE2', 'DATE3');
      foreach ($_POST['searchBuilder']['criteria'] as &$value) {
          if (in_array($value['origData'], $postNameArr) && $value['type'] === 'date') {
              foreach ($value['value'] as &$item) {
                  $date = date_create_from_format( 'Y-m-d', $item );
                  $item = date_format( $date, 'Ymd' );
              }
              foreach ($value as $key => &$item) {
                  if (preg_match('/(value.)/', $key)) {
                      $date = date_create_from_format( 'Y-m-d', $item );
                      $item = date_format( $date, 'Ymd' );
                  }
              }
          }
      }
  }
  
• colin Posts: 15,177Questions: 1Answers: 2,590

  November 2021

  Excellent, thanks for posting back,

  Colin