Can't Filter Table with DateTimePicker

[mathfour]

I need to make the datetimepicker work as a filter on a table pulling datetimes. However, I only need to filter on dates, not on times.

The data source includes times (that aren't all the same), and the datetimepicker can be set up to only use dates (I think). I figured I could shave off the time part of the date with something like CONVERT(SL.shipment_date, getdate()) and/or moment.utc(SL.shipment_date).format('MMMM D, YYYY') but neither are working.

I've found a workaround for the time being, but I don't like it. Here is the workaround:

1. Use CONVERT(varchar(10), SL.shipment_date to get the date into a string.
2. Instead of datetimepicker1.value, use datetimepicker1.formattedString so I can compare strings.

This formats the query result and the thing to which it compares to both be strings.

The problem with this is that the datetimepicker isn't the nice MM/DD/YYYY that we are used to. It is this unattractive, non-intuitive thing:

I need a way to bring in the datetime in my query and compare it to the datetimepicker as a date, not as a string.

Thanks, y'all!

[justin]

Hey @mathfour! Using Moment is working for me (example below) - are you getting an error on your end?

[mathfour]

There is no error, it just doesn't work.

I tried moment.js and it returns no rows:

I'm not sure you understood my question, though. I need to filter when shipment_date = datetimepicker_shipment_date. The shipment date is in a datetime format:

Since the Retool datetimepicker's value is also in datetime format, I would think my code above (with moment.js) should work, the only problem being the times involved:

I have tried various ways to pull in the date, but I can find the flavor of SQL that Retool uses:

There are a number of possible solutions here, I've tried all the ones I can think of. Please help.

[mathfour]

Thanks @Tess for helping me out on this one via chat. I'm posting the solution that finally worked, although there's another strange time issue now (that you can see here: Query of dates use time zones... but only sometimes!).

I can use this to filter:

Then use this to select and display:

And keep my datetimepicker looking nice:

[Craig_Madsen]

I've got a similar question/issue like this one that

1. I've tried this:
   select e."Date", CONVERT(varchar(10),e."Date") as [Short_Date] from entries e
   --syntax error at or near ","

2. And this:
   select e."Date" from entries e WHERE e."Date".format('YYYY-MM-DD') = {{ moment(datePicker1.value).format('YYYY-MM-DD') }}
   --cross-database references are not implemented: e.Date.format. Position 38

[Craig_Madsen]

I got it to work like this:

SELECT * FROM entries e
WHERE date_part('year', e."theDate")||'-'||LPAD(date_part('month', e."theDate")::TEXT,2,'0')||'-'||LPAD(date_part('day', e."theDate")::TEXT,2,'0') = {{ moment(datePicker1.value).format('YYYY-MM-DD') }} ORDER BY e."theTime"