I have a script to auto close tickets, but I don't want to have to keep updating the SQL query with the date.

Using the custom report builder, I have:

Display Columns - Request ID

Request Status - is not 'Closed', AND

Created Time - less or equal '2021-08-31', AND

Last Update Time - less or equal '2021-08-31'.

Group By - Group

Order By - Request ID

Which results in this query:

SELECT "qd"."QUEUENAME" AS "Group", "wo"."WORKORDERID" AS "Request ID"  FROM "WorkOrder" "wo" LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID" LEFT JOIN "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID" LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID" LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID" WHERE  ( ( ( ( "std"."STATUSNAME" != N'Closed' ) OR ( "std"."STATUSNAME" IS NULL ) ) AND ( ( "wo"."CREATEDTIME" <= 1630450799999 ) AND ( ( ( "wo"."CREATEDTIME" != 0 ) AND ( "wo"."CREATEDTIME" IS NOT NULL ) ) AND ( "wo"."CREATEDTIME" != -1 ) ) ) ) AND ( ( "wos"."LAST_TECH_UPDATE" <= 1630450799999 ) AND ( ( ( "wos"."LAST_TECH_UPDATE" != 0 ) AND ( "wos"."LAST_TECH_UPDATE" IS NOT NULL ) ) AND ( "wos"."LAST_TECH_UPDATE" != -1 ) ) ) )  AND wo.ISPARENT='1'  ORDER BY 1, 2

Basically, I need to change the longdate '1630450799999' to something that will dynamically read a year ago from whenever the query is ran.

( "wo"."CREATEDTIME" <= <to_lastyear> )

but this isn't 1 year ago from today, it is anything 2021 so it includes everything up to 31/12/2021.

I tried:

( "wo"."CREATEDTIME" <= DATEADD(year,-1,GETDATE()) )

but this results in an error:

"com.microsoft.sqlserver.jdbc.SQLServerException: Arithmetic overflow error converting expression to data type datetime."

I tried:

( "wo"."CREATEDTIME" <= datetolong(DATEADD(year,-1,GETDATE())) )

but this results in an error:

"java.lang.Exception: Syntax error : near (DATEADD(year,-1,GETDATE()"

-----

Edit:

I figured it out.