I’m dealing with a dataset imported from a program to Google Sheets, and it contains timestamps formatted like 1/2/2020 8:56. I know that Google Sheets queries need dates in the yyyy-mm-dd format, so that’s a challenge I’m facing.
Currently, I am using the following query to retrieve the data I need:
=query(RawDataUpload!A:I, "select * where A is not null and A >= date '2020-01-02' and B = 'Buying' and H > 0 and H < 50000 Order by D, H desc")
While this works, I must update the date manually each day, which is inconvenient. I want to use the Today() or Now() functions to filter results from the last 12 or 24 hours, but I’m encountering errors.
For instance, I tried this:
=query(RawDataUpload!A:I, "select * where A is not null and A >= date '"&TEXT(TODAY(),"yyyy-mm-dd")&"' and B = 'Buying' and H > 0 and H < 50000 Order by D, H desc")
Could you help me find a way to integrate the Today() or Now() functions into my queries to automatically filter the data by date and time?
quick fix that worked for me - wrap your TODAY() in DATEVALUE function like this: A >= DATEVALUE(TODAY()). googel sheets sometimes gets confused with date types but DATEVALUE forces it to recognize properly. been using this for months without issues.
I ran into similar timezone issues when working with imported data that had mixed date formats. The problem with your original approach is that Google Sheets Query function can be finicky about date comparisons when your source data includes time stamps. What solved it for me was using the DATE function to extract just the date portion from your timestamp column for comparison. Try this query instead: =query(RawDataUpload!A:I, "select * where A is not null and DATE(A) = DATE(TODAY()) and B = 'Buying' and H > 0 and H < 50000 Order by D, H desc"). This compares just the date portions and avoids the formatting headaches entirely. If you need the last 24 hours rather than just today’s date, you can modify it to DATE(A) >= DATE(TODAY()-1). This approach has been much more reliable in my experience than trying to format dates as strings for the query parser.
Had this exact problem last month when building automated reports. The issue you’re running into is that your timestamps include both date and time components, but your query is only comparing against dates. When you use date '2020-01-02', it’s comparing against midnight of that date, so any entries from later in the day won’t match properly. Instead of using the date keyword, try using datetime comparisons. This worked for me: =query(RawDataUpload!A:I, "select * where A is not null and A >= datetime '"&TEXT(NOW()-1,"yyyy-mm-dd hh:mm:ss")&"' and B = 'Buying' and H > 0 and H < 50000 Order by D, H desc"). The key difference is using NOW()-1 to get the last 24 hours and formatting it with time included. You might need to adjust the time zone handling depending on your data source, but this approach should eliminate the manual date updates you’re currently doing.