Databricks Query

[Miotx]

Hello All!,

I have a workflow with a query to a Databricks resource. Everything works fine when I try to query with a date in the query but if I try to use moment js to basically display yesterdays date then it doesn't display. Can I use moment js in a databricks query?

Here is my working example:

select totalnetsales from datashare where businessdate >= '2024-09-22' group by all

Here is what I want to achieve but it never displays the date:

select totalnetsales from datashare where businessdate >= '{{ moment().subtract(1, 'days').format('YYYY-MM-DD') }} group by all

Any help would be appreciated!

Thanks!

[ZeroCodez]

Hi @Miotx, You cannot use GROUP BY all. List those columns after ' GROUP BY ' if you want to group by specific columns. If grouping isn't needed, remove the GROUP BY clause.

SELECT totalnetsales, businessdate
FROM datashare
WHERE businessdate >= '{{ moment().subtract(1, "days").format("YYYY-MM-DD") }}'
GROUP BY businessdate

[Miotx]

Hello!

I tried doing that and even removed the group by filter but it still didn't work. If I hover over the entire query it shows the date as a question mark as shown below but if I hover over the moment query then it does show yesterdays date correctly.. its strange!

Thanks!

[ZeroCodez]

Ensure that you have data for the previous day from the current date. It's working on our end, but since we don't have recent data in the database, we’ve subtracted 700 days.

[Miotx]

Thanks again for your response. I notice you are querying Retool Database but I am using Databricks so I think that's why its working for you but not for me.

When using the Databricks resource, I am unable to use any sort of {{ }} values.

Below is a screenshot showing if I put the date in manually and it works fine:

And here is a screenshot of when I am trying to use moment to insert yesterdays date:

Thanks,

[ZeroCodez]

In the Databricks resource settings, you can enable the prepared statements option to utilize dynamic JavaScript within your query.

[Miotx]

Hi @ZeroCodez

Thanks for your reply! I made the change in my databricks resource but unfortunately it still doesn't work!

thanks!