Dynamic SQL statement in data source through scripting

[Aaron Munoz]

I want to create a SQL statement that uses two different datetime fields to pull delta records. I know there is a field called "Use last run datetime", but it can only use one field, and only imports records where the datetime field is between the last import and the lastest datetime in the chosen column. It would be something like this pseudo code:

IMPORT id
FROM my_table
WHERE (
(created_dt BETWEEN {ts gs.now()} AND {ts gs.now()})
OR
(approved_dt BETWEEN {ts gs.now()} AND {ts gs.now()})
)

the challenge is how to add a function like gs.now() inside the query that runs daily.

[SanjivMeher]

Below should help. You dont need servicenow functions there. You should need sql date functions

https://dba.stackexchange.com/questions/208555/how-to-extract-data-between-yesterday-and-today

[Mike Allen]

gs.now is:

DECLARE @LastChangeDate as date
SET @LastChangeDate = GetDate()

and you can put @LastChangeDate in your between where clause.

[Aaron Munoz]

Thanks for the suggestions so far. I like the idea of using native functions, though GetDate() doesn't seem to work in Teradata. How would I perform a query in Teradata like the following:

SELECT *
FROM my_table
WHERE created > 'seven_days_ago' OR approved = 'today'

[Manish Vinayak1 · Accepted solution]

I think the following documentation should be helpful:

https://docs.teradata.com/reader/kmuOwjp1zEYg98JsB8fu_A/W2ZJsPFeR7EFlyBUnQWrEw

https://teradata.github.io/presto/docs/0.167-t/functions/datetime.html

I haven't worked on Teradata SQL, but from looking at the documentation my guess would be:

SELECT *
FROM my_table
WHERE created > current_date - interval '2' day OR approved = current_date