OVERLAY, which is commonly used for strings, has also been extended to allow operating on DATE, TIME, and TIMESTAMP values.

 

Syntax

 

OVERLAY (

 <date, time, or timestamp value expression> PLACING <integer or float value expression>

 IN {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }

 

 

Usage

 

The function allows to replace parts of a datetime value with another value directly in place.

 

For YEAR..MINUTE the PLACING expression is interpreted as an integer expression, for SECOND as a float expression, containing both seconds and milliseconds as a fraction.

 

The value being placed must result in a valid date/time (so HOUR must be 0..23, MINUTE or SECOND 0..59.9999, DAY can't be beyond the end of the specific month, and so on).

 

When a YEAR or MONTH is set that would result in the existing DAY component to be beyond the valid range for that month (e.g. 29th Feb when not a leap year, or 31st Apr) then the DAY value is clamped to the last day of that month.

 

Here is an example of what is now possible:

 

SELECT

  OVERLAY(CURRENT_DATE PLACING 2020 IN YEAR),

  OVERLAY(CURRENT_DATE PLACING 1 IN MONTH),

  OVERLAY(CURRENT_DATE PLACING 2 IN DAY),

  OVERLAY(CURRENT_TIME PLACING 3 IN HOUR),

  OVERLAY(CURRENT_TIME PLACING 4 IN MINUTE),

  OVERLAY(CURRENT_TIME PLACING 5.1234 IN SECOND),

  OVERLAY(DATE'2004-02-29' PLACING 2005 IN YEAR),

  OVERLAY(DATE'2004-01-31' PLACING 4 IN MONTH)

FROM

  #dummy;