Skip to main content
Question

How to use SQL in additional filter to update a date time column?

  • May 3, 2024
  • 2 replies
  • 17 views
hamood.farid11
Level 1

I have a table were I only want to extract last 20 days of data based on "Update" datetime column. As the parameter option has a fixed start time (default value), I am looking for an option to add a SQL in Filter Statement where it substract 20 days from current date. This is giving error "Error parsing filter:"

SQL used in Filter Statement: ColumnName >= TIMESTAMPADD ('day', -20, CURRENT_TIMESTAMP AT TIME ZONE 'US/Eastern')

As this is Full load extraction, I am not using "Enable Change Date Filter".

 

I also tried the option of creating parameters and changing value in transformation but that too didn't work.

Any help would be appreciated.

2 replies

dennis.pflug
Level 10

Hi Hamood,

 

not the nicest solution but at least a workaround.

Create a transformation that runs before your data extraction.

Within this transformation create a dummy table that only contains the expected date.

 

Make use of this dummy table within a parameter (dynamic parameter that uses the dummy table to get the date written into the table with your SQL query).

 

Use this parameter ColumnName >= <%=dateFromDummyTable%>

 

BR

Dennis

 

 

hamood.farid11
Level 1

Thanks for the response, @dennis.pflug!

 

As the data job has not been moved to Delta Extraction yet, the solution you suggested is a great way to accomplish the task.

 

I just tweaked a little bit.

I am using the expected date value in Data Pool Parameter and sharing the value across two different source system extractions.

 

My Best

Hamood

Community Code of ConductAccessibility statement