Round up/down date

Converts date values from a date column to a rougher time unit and writes the results to a target column.

The following parameters are available.

Action

Result

Source column

Name of the source column whose values are rounded.

Source: Source table

Data type: Date

Specification: Mandatory

Precision

Accuracy of the new date format, defined by the unit: Year, Quarter, Month, Day, Hour, Minute, or Second, and Interval: Depending on the selected unit, for example, 5 minutes or 1 year

Data types: Numeric, Text

Default values: 1, Minute

If the accuracy of the source column is less accurate or the same as the target column format, the original value is retained.

The date values are rounded according to the selected interval. Only the unit to be rounded is taken into account, for example, when rounding to minutes, the seconds are ignored.

Rounding type: Specifies how the selected time interval is to be rounded.

Round up for half an interval: Automatically rounds up above an interval value higher than or equal to half of the interval value

Round down for half an interval: Automatically rounds down below an interval value lower than or equal to half of the interval value

Always round up: Always rounds up, regardless of the interval value

Always round down: Always rounds down, regardless of the interval value

Target column

Name of the target column to which the converted date is written

Data type: Date

Default value: Result_1

Specification: Optional

The target column can be identical to the source column. The values in the target column are overwritten.

If the target column is not of the Date type, it is replaced by a new date column.

Examples

Source value

Accuracy

Result

2009-12-24T16:23

Day

2009-12-24

2009-12-24T16:23

Hour

2009-12-24T16

2009-12-24

Month

2009-12

2009-12-24

Quarter

2009-Q4

2009-12-24

Year

2009

Rounding

2010-08-06T17:15:27

10 seconds

2010-08-06T17:15:30

2010-08-06T17:07:00

15 minutes

2010-08-06T17:00

2010-08-06T17:18:00

15 minutes

2010-08-06T17:15

2010-08-06T02:18:04

4 hours

2010-08-06T04:00:00

Round up

Source value

Accuracy

Result

2010-02-28T23:07:00:00 AM

15 minutes

2010-02-28T23:00

2010-02-28T23:07:30

15 minutes

2010-02-28T11:15:00 PM

2010-02-28T23:30:00

1 hour

2010-03-01T00

Round down

Source value

Accuracy

Result

1970:01:01T09:00:01

6 hours

1970:01:01T12:00:00

1970:01:01T09:00:00

6 hours

1970:01:01T06:00:00

Always round up

Source value

Accuracy

Result

2010-02-28T11:15:00 PM

15 minutes

2010-02-28T11:15:00 PM

2010-02-28T11:15:01 PM

15 minutes

2010-02-28T11:30:00 PM

2010-02-28T20:00:01

6 hours

2010-03-01T00

Always round down

Source value

Accuracy

Result

2010-02-28T11:15:00 PM

15 minutes

2010-02-28T11:15:00 PM

2010-02-28T11:14:59 PM

15 minutes

2010-02-28T23:00

2010-03-01T05:59:59

6 hours

2010-03-01T00