Finding values with a date that is in the current quarter

Queries and Resources
,
1

Hello,

I have a Query JSON with SQL query and would like to filter out dates that are not within the current quarter. The source data ({{dailySubmissions.data}} has 4 keys of data: Name, Date, Region, Site, and I am only interested in the Name and Date.

I currently have:

SELECT Name, Date FROM {{dailySubmissions.data}}
WHERE DATE BETWEEN ???

I have tried setting the dates like so:

SET todaysDate = GETDATE();
SET startDate = DATEADD(q,DATEDIFF(Q,0,todaysDate),0)
SET endDate = DATEADD(d,-1,DATEADD(q,DATEDIFF(q,0,todaysDate) +1, 0))

and trying to put DATE BETWEEN startDate AND endDate but I don't think that is right.

2

Try using moment()
https://docs.retool.com/docs/javascript-in-retool#dates
And here is a JSON(import it as an app) that someone built and posted in the forum a while back - can't take credit for it...but I found it useful
Timeframe.json (8.7 KB)

3

I actually figured it out and it has been working seamlessly so I will add it here in case anyone else has this question!

Basically, I wrote a Javascript query named dateArray that outputted a list of dates in the current quarter, like so:

var today = new Date();
var quarter = Math.floor((today.getMonth() +3) / 3);
var year = today.getFullYear() + (quarter == 1? 1 : 0);
let text = 'Q';
let currentQuarter = text.concat(quarter,"-",year);
const startCurrentQuarter = new Date(today.getFullYear(), quarter * 2, 1);
const endCurrentQuarter = new Date(startCurrentQuarter.getFullYear(), startCurrentQuarter.getMonth() + 3, 0);

var getDaysArray = function(s,e) {for(var a=[],d=new Date(s);d<=new Date(e);d.setDate(d.getDate()+1)){ a.push(new Date(d));}return a;};

var dateArray = getDaysArray(startCurrentQuarter,endCurrentQuarter);
dateArray.map((v)=>v.toISOString().slice(0,10)).join("")

return dateArray;

Then, the SQL query looked like:

SELECT * FROM {{source.data}}
WHERE DATE IN ({{dateArray.data}})