Netezza / PureData – how to set processing date to next day based on a timestamp

hey are a variety of uses for this logic within Netezza / Puredata SQL, when working with interfaces, ETL, ELT, Snapshot facts and/or tables, setting the processing / run control date, and the list goes on.  However, rather than reinventing this logic each time, I thought I would save this code snippet for future use and reference.

What this code does is straight forwarded enough, it:

  • Determines the current time of day using the Now()
  • Determines the our from the time using the Date_Part function
  • Determines the current date using the current_date function
  • Then, using the Case function set the processing date based on the current hour of the day

SQL used to calculate processing date

Select

now() as “Time”,

current_date as “Today”,

(case

when (DATE_PART(‘HOUR’,NOW())>12 )

THEN date(current_date +  cast(‘1 days’ as interval))

ELSE  current_date

END ) as “PROCESSING_DATE”

From ADMIN._v_dual;

·       Morning Example of SQL Results

·       Afternoon Example of SQL Results

Related References

%d