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:
Use CONVERT(varchar(10), SL.shipment_date to get the date into a string.
Instead of datetimepicker1.value, use datetimepicker1.formattedString so I can compare strings.
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.
I've got a similar question/issue like this one that
I've tried this:
select e."Date", CONVERT(varchar(10),e."Date") as [Short_Date] from entries e
--syntax error at or near ","
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
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"