Using date funtion in query

locomot1f

Greetings,

I’ve seen some post that are close to my question, but not exactly.

I’ve been trying to use this type of query within one of my Workflows:

=query(Airtable_CASA!A:H,"select * where A > date '"&text(datevalue("4/15/2023"),"yyyy-mm-dd")&"' ORDER BY A",1)

When I look to input it into my Workflow like this:

SELECT * WHERE A > date '"&text("4/15/2023","yyyy-MM-dd")&"' ORDER BY A

It gives me the error :

thanks in advance for any assistance.

talk soon.

Karoline from Sheetgo

Hi Ben,

Please, could you test it with this format: “2023-04-15”

Let me know if it worked

locomot1f

yes, that format works. but i’m trying to input dates that might not always have that format.

it works if i’m just inputting this query in google sheets. it seems that the Workflow does not allow for the extra quotation marks to allow for these functions.

even if I try to use the Conditional Format on my date Column, it does not return any results

the one caveat that I might be running into is that I’m doing this after a Merge of two sheets:

please let me know if you need anything else from me.

talk soon

Karoline from Sheetgo

locomot1f

Our filter by query is similar to a SQL. Unfortunately, doesn’t work with other date format. You can check the oficial documentation through this link .

The filter by condition should work after the merge. You can use the same command:
Column A - Date - is after: (Choose Exact date).

If you’'d like to share the workflow with me, I’ll be happy to take a look!

To share: Enter our web app, click on the workflow, and then on “share settings.” Add my email karoline@sheetgo.com, press ↹ (tab key), and make sure you enable “manager access” and check the "enable file editing"check box

locomot1f

thanks for all your assistance Karoline.

Please let me know if you have any questions about my workflow.

Quick question, I also asked when I shared the file:

Does the date format in my GSheet need to be in a certain format? Like I said, I import this data, and this is how the dates come in:

Also, I have read through the Google site you sent me. That’s what lead me to sites like this one - https://www.benlcollins.com/spreadsheets/query-dates/

That show how to get around this limitation. It just doesn’t seem to work when I try to incorporate it into my connection that I was showing in my first post.

locomot1f

some more testing

if I run my connection with no Filtering (just merging two sheets)
my data come out very clean - what I expect:

When I add a simple Filter Query:
Select *
I noticed it changes my Date column to a number:

I’ve run into this before, it doesn’t matter what I switch the Parse Data to. I can only assume it’s because I’m exporting it to a CSV File. My only recourse is to create ANOTHER tab, and then export that data. Not a deal breaker, but would be nice to do everything in the same connection, and not have to create another seemingly useless tab.

Karoline from Sheetgo

locomot1f Hi Ben,

Yes, if you merge two sheets without any filter and with the parse toggle on, Sheetgo will just paste the raw data, exactly as they are. Working with a CSV we have some troubleshooting here, in this article: https://support.sheetgo.com/import-date-formatting-from-excel-or-csv-files

It is important to note that when adding filters, particularly the query function, it is necessary to communicate with the query language for the command to function properly. We understand your frustration with these settings and are here to assist you

Please, could you try the trouble shooting that I mentioned? ;)

Hi @Willian from Sheetgo do you have some other tips?