Answers

• kthorngren Posts: 21,327Questions: 26Answers: 4,949

  February 2022

  Maybe I'm not understanding. There aren't any dates with August 11th 2013 in the table. I changed one of the rows with this date. I type in 08/11/2013 and the row with that date shows along with a couple others with later dates.
  http://live.datatables.net/kilayuce/217/edit

  Let me know what I'm missing.

  Kevin
• nport Posts: 23Questions: 6Answers: 0

  February 2022 edited February 2022

  @kthorngren my bad... I believe I included an incorrect link to test case.

  This is the one I meant to link: http://live.datatables.net/topuhayo/6/edit
• nport Posts: 23Questions: 6Answers: 0

  February 2022

  @kthorngren if you use the test case I just linked the issue is if you put a start date in the filter if there are any records with that exact date (or less) they will not show up, but after you have changed the values a couple times it will work.
• sinfuljosh Posts: 28Questions: 0Answers: 5

  February 2022

  I am not able to test in the live datatable from my phone. But it would like there’s an overlap based off of the time aspect of the DateTime.

  If you can zero out the time portion from the min/max then you can ensure that it grabs that entire day.

  (Anytime I am stuck doing date ranges, I make sure it’s only looking at the Date and then adjust the min/max to subtract/add a day and use < and > in the filtering (no <= and >= ).

  This ensures that the query grabs every item within the selected date range.

  I hope this helps and I’ll check to see if still need help when I’m back at my computer.
• kthorngren Posts: 21,327Questions: 26Answers: 4,949

  February 2022

  @sinfuljosh is correct. Using the DateTime picker seems to set the time to the local time when not choosing it from the input. For example: 2013-08-11T07:09:28.000Z.

  Not sure how to zero out the time. @allan or @colin can comment. Also I wonder if the DateTime picker should automatically zero out the time if only choosing the date.

  Kevin
• nport Posts: 23Questions: 6Answers: 0

  February 2022

  @sinfuljosh @kthorngren okay thank you for the insight, I will see if I can figure out how to zero out the time.

  If either of you find a solution I would appreciate any help
• nport Posts: 23Questions: 6Answers: 0

  February 2022

  @sinfuljosh Did you get a chance to check it out? I have had no luck, I just keep running into more problems. I was able to remove the time using a couple different methods such as .toLocaleDateString(), but I ran into new problems that I believe have to do with timezone issues. The dates in the table and filter input are sometimes off by one day, which I think may be causing the issue
• sinfuljosh Posts: 28Questions: 0Answers: 5

  February 2022

  I am actually looking at it right now. And that is what it is doing.

  The two input fields return Dates with Times attached.
  "Min: Mon Jan 31 2022 18:00:00 GMT-0600 (Central Standard Time)"
  "Max: Sat Feb 19 2022 09:36:15 GMT-0600 (Central Standard Time)"

  The data in the table returned a date with the hours set to 0.
  "Date: Fri Jun 01 2012 00:00:00 GMT-0500 (Central Daylight Time)"

  Working on the best way to address this, since i still suck at programming. (i suck at javascript)
• sinfuljosh Posts: 28Questions: 0Answers: 5

  February 2022 edited February 2022

  If someone that is familiar with JS can help out on this. On my setup we used timestamps so it was easier to deal with. I used moment.js to convert it to timestamp and it just filtered like a number. But the strange thing I noticed on your link is that that the time is off.

  I think there is a bug in editors DateTime, if I change the format to include time. The input value and the console log show two different values.
  
• nport Posts: 23Questions: 6Answers: 0

  February 2022

  @sinfuljosh Okay I came up with something, definitely not the prettiest solution but it works. I ended up not even zeroing out the time component of the date object, I just just changed the inputs for the start and end date to be type="date" from type="text" like so:
  <input type="date" id="min" name="min">
  I then initialized the date column and the 2 inputs all as new Date() objects.

  Here is the link to my updated code: http://live.datatables.net/topuhayo/12/edit
• sinfuljosh Posts: 28Questions: 0Answers: 5

  February 2022 edited February 2022 Answer ✓

  @nport Thanks for that update. I dont know why I used what you posted above and was able to use datatables DateTime input. What was messing me up earlier was it not liking the format used in the input, changing that and using your if null got it working.

  Original: http://live.datatables.net/topuhayo/6/edit
  Working: http://live.datatables.net/polaloto/1/edit

  Changed original lines 6-7 to:

          let min = ($('#min').val() == '') ?
              null :
              new Date( $('#min').val() ).setUTCHours(0,0,0,0);
          
           let max = ($('#max').val() == '') ?
              null :
              new Date( $('#max').val() ).setUTCHours(23,59,59,999);
  

  Changed original lines 29-35 to :

      // Create date inputs
      minDate = new DateTime($('#min'), {
          format: 'MM/DD/YYYY'
      });
      maxDate = new DateTime($('#max'), {
          format: 'MM/DD/YYYY'
      });
  

  The one further I would look into is top edit so that it checks for a valid date and if its not, then return null.
  UPDATE: If you are using moment.js you can use their isValid to check for a valid date or else return null (stops the datatable from filtering on invalid dates)

  Updated from original lines 6-7:

          let min = moment($('#min').val()).isValid() ?
              new Date( $('#min').val() ).setUTCHours(0,0,0,0) :
              null;
          
           let max = moment($('#max').val()).isValid() ?
               new Date( $('#max').val() ).setUTCHours(23,59,59,999):
               null;
          var date = new Date( data[4] );
  
• nport Posts: 23Questions: 6Answers: 0

  February 2022

  @sinfuljosh awesome thank you for the update as well! This issue took me a lot longer than I would like to admit lol, thank you for your help I really appreciate it it seems like everything is working as you would expect now
• sinfuljosh Posts: 28Questions: 0Answers: 5

  February 2022

  It was puzzling to me also originally due mainly to the date format I think. But when you changed the formatting and was able to generate the new Date(). It all clicked.

  Now I need to find out how to file a bug report because the date values that the input shows and the dates that the console reports are off. (I think one is reporting UTC and one is not. )