I have a saved query in TMS that I run every day. And every day, I have to change the date field to reflect the current date. Perhaps in the future, Gallery Systems will provide a new relationship “Is Today” for date fields, but until that happens here’s a hack to try.

Let’s start by getting the QueryID for our desired saved query, using the following SQL:

SELECT Q.QueryID, Q.Name, T.TableName AS Module, Q.Owner

FROM Queries Q

LEFT JOIN DDModules M ON Q.ModuleID = M.ModuleID

LEFT JOIN DDTables T ON M.MainTableID = T.TableID 

Use the QueryID to get the QueryDetailID for the search criteria you want to make dynamic, with the following SQL:

SELECT QD.QueryDetailID, C.ColumnName, QD.Value

FROM QueryDetails QD

LEFT JOIN DDColumns C ON QD.ColumnID = C.ColumnID

WHERE QD.QueryID = {Your key}

Finally, test this SQL script using the QueryDetailID you just identified:

UPDATE QueryDetails SET

     Value = CONVERT(NVARCHAR(10),GETDATE(),126)

WHERE QueryDetailID = {Your key}

When you open up your saved query, you will now see that the date field reflects the current date. If you have more than one field you want to be updated, use the IN operator in your WHERE clause. Or experiment with the DATEDIFF function for updating a field to reflect a date five days ago or five days in the future.

Automating the script

Create a Job under your SQL Server Agent in SQL Server Management Studio, and give it a meaningful name like “TMS: Update Saved Query Dates”. Add a Step, and copy in the script you tested above.

Now that you have the Step created, add a Schedule to run everyday at midnight.

Now, enjoy the rewards of having a saved query that is always current.

Thanks for taking the time to read my post – I hope you found it helpful.
Leave feedback in the comments below.

If this post save you time, then consider sharing it with your friends and colleagues. If it saved you some money, then please consider buying me a coffee. If you want to know when I publish new content, then subscribe to my feed. Your support encourages me to write more. Thanks!