Hello, I have a date time column that looks like this: “05.Jun.2020 10:58:35.893”. I want to filter my data to only return the lines that were in the previous 24 hour period. So If today is June 10th, I want only the lines from June 9th or 09.Jun.2020 00:00:00.000 to 09.Jun.2020 23:59:59.999.
I’ve tried to use a few different DateTime nodes (like Date&Time Difference) and I get this error when I try to configure:
The dialog cannot be opened for the following reason: No column in spec compatible to “LocalDateValue” “LocalTimeValue” “LocalDateTimeValue” or “ZoneDateTimeValue”
Can someone please point me to correct way to handle this?
First you have to use “String to Date&Time” node (the date format is dd.MMM.yyyy HH:mm:ss.SSS ) and then the “Extract date&Time Fields” where you can extract month, day, hour,…
Thank you for your reply but here’s the new problem I’m seeing, when I try to do the “String to Date&Time” it doesn’t recognize the column (“TicketCloseTime” )I’m trying to convert as text . See below.
At the same time, when I try to use the “Modify Time” node, or any other node to modify a date/time column, it doesn’t recognize it as a Date/Time column. See below, It doesn’t show any Date/Time field available to edit…??? Help! What am I missing here?
The node is named “String to Date & Time” because it takes a column of data type String and creates cells in a colum of data type something-involving-date-and-or-time.
This is why the usable columns listed in that screen shot all have a S in a white box prefixing them, and the reason why your TicketCloseTime is not usable.
It sounds like you want a node that does the opposite conversion? If so:
Hi,
The icon near the “TicketCloseTime” is a calendar with a clock that means taht is alredy recognized as Date&Time format .This is the reason why “String to Date&Time” it doesn’t recognize it.
You can already use “Extract date&Time Fields” to extract the field you want.
Thank you everyone for your replies. Here’s the conundrum- the “TicketCloseTime” looks like a date and time column but when I try any node that manipulates date/time including the “Extract date&Time Fields” as suggested by @andrejz I get this error below when I try to open the “Configure”:
as @quaeler pointed out you have a legacy Date&Time format. Convert it to new Date&Time format and you’ll be able to use above mentioned nodes as expected.
Thank you @quaeler ! that worked perfectly. Now I have to figure out the best node to be able to filter to now minus one day. In other words I need to filter to just yesterday’s date. Any suggestions?