How To Pass Dates From SQL Server to ServiceNow

[johnfeist]

I have connected SQL Server to our ServiceNow instance and am able to execute queries.  Where I run into a problem is passing dates when they are not hard coded.  For example a query like SELECT * FROM OPENQUERY(SERVICENOW, 'SELECT assigned_to, category, caller_id, opened_at where opened_at > ''2018-03-01 00:00:00'' ') works fine.  Of course I don't want to be hardcoding dates, so if I change the query to SELECT * FROM OPENQUERY(SERVICENOW, 'SELECT assigned_to, category, caller_id, opened_at where opened_at > DATEADD(d, -3, GETDATE())') I get errors returned.

I've tried all kinds of permutations none of which are successful.  I'm assuming that the query needs to run against MySQL as SELECT * FROM OPENQUERY(SERVICENOW, select CURRENT_DATE()') returns the correct result.  When I expand it to trying to use DATE_SUB(CURRENT_DATE, INTERVAL 3 DAY) I get syntax errors "on or after the token <3>.

Can anyone tell me what I'm doing wrong so that I can stop wasting time in this.

Thanks in advance for any assistance you can provide.

Best regards,

John

[johnfeist · Accepted solution]

After a lot of time digging through MSFT groups I found the answer, it's not pretty!  In simple terms the query has to be executed as dynamic SQL that populates a table.  The table is typically temp to avoid other headaches.  Of course, if you're keeping SN data to drive other processes, you can have the dynamic SQL do your table maintenance. 

Following is an example that gets the number, opened_at and short_description from incident for incidents opened in the last three days (I'm assuming that if you're reading this you have a reasonable level of SQL Server knowledge):

--Get some starting date

DECLARE @StartDate date = DATEADD(d,-3,GETDATE())

--Now build the query, be careful matching the quotes and the right number of them.  Don't be concerned

--if the IDE flags your column names, just be sure you get them correct for the SN tables 

DECLARE @Query nvarchar(MAX) = 'INSERT INTO #Results SELECT * FROM OPENQUERY( SERVICENOW,''select number, opened_at, short_description

from incident where opened_at > ''''' + CAST(@StartDate AS VARCHAR) + ''''''' )'

--Establish a temp table to hold your results using a variable table can get problematic.   you --If plan to run the code repeatedly remember to add a drop statement first

CREATE TABLE #Results (number nvarchar(20), opened_at datetime, short_description nvarchar(MAX))

--Run the query dynamically

EXEC(@Query)

--Do something with the results

SELECT * FROM #Results

I haven't done any stress testing, but with a reasonable data volume things seem to work pretty well.

[jxsaxton421]

I don't think you had to do what your solution was at all. I have used get Date just fine, but you have to add some variables to hold the date/time. 

Something similar to var current date = getDate() and then use that in your script.