Skip to main content

Run-Time Dates and Times

You can use your warehouse's native date and time functions in Parameters and Column Transforms. Coalesce sends that SQL to the platform when the Job runs, so the database evaluates CURRENT_DATE(), CURRENT_TIMESTAMP(), offsets, and month-end functions at run time instead of freezing a literal in the editor.

Match the column data type

Set the column Data Type to DATE or TIMESTAMP to match what your expression returns. Mixing types can cause compile errors or implicit casts you did not intend.

Use Run-Time Dates in Parameters​

Define a Parameter value with warehouse SQL. When a Job uses that Parameter, the warehouse resolves the expression for that run.

Common choices:

  • CURRENT_DATE()
  • CURRENT_TIMESTAMP()

Example: a Parameter set to CURRENT_DATE() returns the calendar date for each Job run.

Use Run-Time Timestamps in Column Transforms​

Use the same functions in the Transform field or Column Editor when you want audit-style fields that refresh every time the Node runs.

CURRENT_TIMESTAMP()

Cast to TIMESTAMP_NTZ, TIMESTAMP_LTZ, or TIMESTAMP_TZ when you need a specific timestamp variant for governance or downstream tools.

These patterns are typical for CREATED_AT or UPDATED_AT style columns.

Build Incremental-Style Windows​

For run-relative windows such as yesterday or T-1, use each engine's date math in filters, Parameters, or transforms.

CURRENT_DATE() - 1

You can use similar arithmetic for other offsets, for example CURRENT_DATE() - 7 for a 7-day lookback.

Month End From a Source Date​

To derive the last calendar day of the month from a row's date column, wrap the source expression your Node already exposes, such as {{SRC}} from Helper Tokens.

LAST_DAY(CAST({{SRC}} AS DATE))

LAST_DAY accepts an optional second argument for other periods; the default date part is MONTH, which returns month end.

If the source value is NULL, LAST_DAY returns NULL.

Month End From Today​

For logic tied to the Job run date, compute month end from the warehouse's current date in the same dialect.

LAST_DAY(CURRENT_DATE())

Calendar Month End and Fiscal Periods​

LAST_DAY and last_day return calendar month end. Fiscal period end depends on your organization's calendar. If fiscal months do not line up with calendar months, model those boundaries in a calendar table or mapping and join to it instead of relying on LAST_DAY alone.

Other Common Uses​

Run-time dates and times also support:

  • Incremental loads - Filter or watermark with relative dates so each run uses the correct window without editing literals.
  • Partition keys - Populate date or timestamp partition columns from CURRENT_DATE() or current_date() so new data lands in the right slice.
  • Run metadata - Stamp processing time with CURRENT_TIMESTAMP() or current_timestamp() in logging or audit tables.

Limitations​

Some testing utilities only accept fixed date strings and reject run-time date functions. For example, testUtils.test_missing_dates expects a static value rather than CURRENT_DATE().

What's Next?​

  • Column Transforms for transform fields, data types, and platform quoting.
  • Helper Tokens for {{SRC}} and related tokens in transforms and Macros.
  • Macros to reuse date logic across Nodes.