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

• Netezza / PureData – Current Date Function
• IBM® PureData™ System for Analytics, Version 7.2.1 – Date/time functions
• IBM PureData System for Analytics, Version 7.2 – Functions
• IBM® PureData™ System for Analytics, Version 7.2.1 – Cast conversions
• IBM PureData System for Analytics, Version 7.2 – Summary of Netezza casting