Use the up and down arrows to select a result. Press enter to go to the selected search result. Touch device users can use touch and swipe gestures.
Version CloverDX 6.7.0

Date manipulation steps

Current date and time

The Current date and time step writes current date and time into designated column. Same value will be written into each row in dataset.

Parameters

  • Target column: required, configure the column which will receive the current date and time. Output will always be of date type.

    • Overwrite existing column: overwrite the input column with the result.

    • Create new column with name: create a new column with specified name. Name of the new column can contain spaces or special characters - technical column name will be created automatically. The new column will be placed at the very end of the dataset.

Examples
Output value Description

2023-03-06 12:34:16.285

Current date and time
Remarks

  • The step will return the same value for each row in the data set regardless of how long the job execution takes. The value written is the date and time when the Wrangler job was started.

  • The value returned is with millisecond precision.

See also
Date add/subtract

The Date add/subtract step adds or subtracts a specified number of time units (days, weeks, months, years, hours, minutes, seconds, milliseconds).

Parameters

  • Input column: required, a date column to add specified number of time units to.

  • Number of units: required, the number of units to be added or subtracted from the selected date. Use negative values to subtract from the date.

  • Unit: required, the time unit to be added/subtracted. Multiple options are allowed:

    • days

    • weeks

    • months

    • years

    • hours

    • minutes

    • seconds

    • milliseconds

  • Target column: required, configure the column which will receive the output.

    • Write result to the current column: outputs data into the Input column.

    • Create new column with name: create a new column with the specified name. Name of the new column can contain spaces or special characters - the technical column name will be created automatically. The new column will be placed right next to the Input column.

Examples

Sample date: 2023-01-09 03:11:00

Input date Unit Number of units Result Note

2023-01-09 03:11:00

Days

8

2023-01-17 03:11:00

2023-01-09 03:11:00

Days

-8

2023-01-01 03:11:00

2023-01-09 03:11:00

Months

2

2023-03-09 03:11:00

2023-01-09 03:11:00

Days

60

2023-03-10 03:11:00

Note the difference between adding 2 months vs. 60 days - the months add calendar months so add different number of days depending on the input date.

2023-01-09 03:11:00

Minutes

123456

2023-08-02 21:02:00

Any number works as number of units - there is no need to limit the values to small numbers.

No value

Any

Any

Error

Trying to add/subtract to/from empty values results in an error.

Error

Any

Any

Error

Applying the step to an Error value results in an Error.
See also
Date difference

The Date difference step calculates the time difference in the specified time unit (days, weeks, months, years, hours, minutes, seconds, or milliseconds) between two date columns.

Parameters

  • First date column: required, select a date column from the column list.

  • Second date column: required, select a date column from the column list.

  • Unit: required, select the date unit that will be used in the calculation (days, weeks, months, years, hours, minutes, seconds, milliseconds).

  • Target column: required, configure the column which will receive the output. Output will always be an integer.

    • Write result to the current column: outputs data into the First date column.

    • Create new column with name: create a new column with specified name. Name of the new column can contain spaces or special characters - technical column name will be created automatically. The new column will be placed next to the First date column.

Examples
First date column Second date column Unit Result Notes

2023-03-31

2023-01-01

Days

89

2023-01-01

2023-03-31

Days

-89

Second date is after the first, result is negative.

2023-03-31

2023-01-01

Months

2

2023-01-01

2023-03-31

Months

-2

Second date is after the first, result is negative.

2023-03-25 04:51:25

2023-03-25 03:00:00

Hours

1

Difference is 1 hour 51 minutes 25 seconds which is truncated to 1 hour.

2023-03-25 04:51:25

2023-03-25 03:00:00

Minutes

111

Difference is 1 hour 51 minutes 25 seconds which is 111 minutes and 25 seconds which is truncated to 111 minutes.

2023-03-25 04:51:25

2023-03-25 03:00:00

Seconds

6685

Difference is 6685 seconds exactly, no truncation is necessary.

No value

Any

Any

Error

Error
Remarks

  • The order of the dates in the step parameters matters. The step always calculates First date - Second date, which might result in negative values if Second date is after the First date.

  • The result is truncated to the whole number of units. For example, if the time difference between the values is 2 hours 45 minutes, the result will be "2" if the time unit is hours.

  • If any of the input dates is No value, the step will fail.

  • If any of the input dates is an Error, the step will fail.

See also
Get part of date

The Get part of date step extracts the specified time unit (day of week, day of month, month, year, hours, minutes, seconds, milliseconds) from a date column.

Parameters

  • Input column: required, select a date column from the column list.

  • Part: required, the time unit to be extracted from input date. Following options are available:

    • day of week

    • day of month

    • month

    • year

    • hours

    • minutes

    • seconds

    • milliseconds

  • Target column: required, configure the column which will receive the output. Output will always be an integer.

    • Write result to the current column: outputs data into the Input column.

    • Create new column with name: create a new column with specified name. Name of the new column can contain spaces or special characters - technical column name will be created automatically. The new column will be placed next to the Input column.

Examples

Sample date: 2023-05-09 03:11:00

Date unit Result

Day of week

2

Day of month

9

Month

5

Minute

11
Returned values

The values returned by the step depend on the selected unit:

Unit Minimum value Maximum value Note

day of week

1

7 (Sunday)

Value of 1 always means Monday and 7 is Sunday regardless of the start day of the week.

day of month

1

31

month

1

12

year

292278994 (292 million)

-292278994 (-292 million)

Dates can store exact date and time millions of years in the past or in the future.

hours

0

23

24-hour format is used regardless of the display format of the input date column (i.e. 4pm will return 16).

minutes

0

59

seconds

0

59

milliseconds

0

999
See also