Chapter 4. Date, Time and Timestamp Transforms

Table of Contents

Date Transform
Add days
Add months
Add years
Date difference (in days)
Day
First of month
Last of month
Max
Min
Month
Month name (long)
Month name (short)
Null to date
Quarter
Quarter name (short)
Set day
Set month
Set year
Subtract days
Subtract months
Subtract years
To string format
Truncate
Year
Time Transform
Add hours
Add milliseconds
Add minutes
Add seconds
Am
Hours
Max
Milliseconds
Min
Minutes
Null to time
Pm
Seconds
Set hour
Set millisecond
Set minute
Set second
Subtract hours
Subtract milliseconds
Subtract minutes
Subtract seconds
Time difference (in ms)
To milliseconds
To seconds
To string format
Timestamp Transform
Add days
Add hours
Add milliseconds
Add minutes
Add months
Add seconds
Add years
Am
Day
First of month
Hours
Last of month
Max
Milliseconds
Min
Minutes
Month
Month name (long)
Month name (short)
Null to timestamp
Pm
Quarter
Quarter name (short)
Seconds
Set day
Set hour
Set millisecond
Set minute
Set month
Set second
Set year
Subtract days
Subtract hours
Subtract milliseconds
Subtract minutes
Subtract months
Subtract seconds
Subtract years
Timestamp difference (in days)
Timestamp difference (in ms)
To milliseconds
To seconds
Truncate
Year

Date Transform

Data in Date type can be converted to Decimal, Integer or String type. You can perform the following operations:

Add days

This operation adds an increment value to the day.

InputIncrementOutput
2009-12-2752010-01-01
2009-12-1452009-12-19

Add months

This operation adds an increment value to the month.

InputIncrementOutput
2009-05-1422009-07-14
2009-12-1422010-02-14

Add years

This operation adds an increment value to the year.

InputIncrementOutput
2009-12-1412010-12-14
2011-12-0112012-12-01

Date difference (in days)

This operation shows the date difference in days between the specified value and the value from the selected field.

InputValueOutput
2011-10-082011-01-01280
2011-11-202011-01-01323

Day

This operation shows only the day value from the selected field.

InputOutput
2009-12-1414
2011-06-2323

First of month

This operation changes the day value to the first day of each month.

InputOutput
2009-12-142009-12-01
2011-06-232011-06-01

Last of month

This operation changes the day value to the last day of each month.

InputOutput
2011-02-032011-02-28
2008-02-032008-02-29

Max

This operation changes each value in the selected field to a specified maximum value. If the original value is less than the specified maximum, the result becomes the maximum, otherwise it is unchanged.

InputMaximumOutput
2011-04-052011-01-012011-04-05
2010-01-012011-01-012011-01-01

Min

This operation changes each value in the selected field to a specified minimum value. If the original value is greater than the specified minimum, the result becomes the minimum, otherwise it is unchanged.

InputMinimumOutput
2011-01-012011-04-052011-01-01
2011-05-012011-04-052011-04-05

Month

This operation shows only the month value from the selected field.

InputOutput
2009-12-1412
2011-06-236

Month name (long)

This operation shows the month value from the selected field in a long name.

InputOutput
2009-12-14December
2011-06-23June

Month name (short)

This operation shows the month value from the selected field in a short name.

InputOutput
2009-12-14Dec
2011-06-23Jun

Null to date

This operation displays the Null dates as a specified value, while the other dates remain the same.

InputValueOutput
 2000-01-012000-01-01
2008-06-232000-01-012008-06-23

Quarter

This operation returns an integer to show the quarter to which the specified date belongs.

InputOutput
2010-10-164
2011-02-071
2011-04-192
2011-07-203

Quarter name (short)

This operation returns a string to show the quarter to which the specified date belongs.

InputOutput
2010-10-16Q4
2011-02-07Q1
2011-04-19Q2
2011-07-20Q3

Set day

This operation enables you to set the day value in the selected field.

InputDayOutput
2011-01-01202011-01-20
2009-07-17202009-07-20

Set month

This operation enables you to set the month value in the selected field.

InputMonthOutput
2011-01-0112011-01-01
2009-07-1712009-01-17

Set year

This operation enables you to set the year value in the selected field.

InputYearOutput
2011-01-0120112011-01-01
2009-07-1720112011-07-17

Subtract days

This operation subtracts a decrement value from the day.

InputDecrementOutput
2009-12-2752009-12-22
2009-12-0552009-11-30

Subtract months

This operation subtracts a decrement value from the month.

InputDecrementOutput
2009-12-1412009-11-14
2009-01-2212008-12-22

Subtract years

This operation subtracts a decrement value from the year.

InputDecrementOutput
2009-12-2722007-12-27
2011-02-0522009-02-05

To string format

This operation transforms dates into a specified String format. Use "dd" for day, "MM" for month, and "yy" or "yyyy" for year. Specify a punctuation to connect the string.

InputString formatOutput
2009-12-27dd/MM/yyyy27/12/2009
2011-01-05dd/MM/yyyy05/01/2011

Truncate

This operation changes the hour, minute and second values to zero. In most cases, the result looks the same with the original dates. However, manipulation has taken place to ensure that there are no hidden hours, minutes or seconds. Therefore, comparing and sorting will remain unaffected by those hidden values.

InputOutput
2009-12-272009-12-27
2010-12-052010-12-05

Year

This operation shows only the year value from the selected field.

InputOutput
2009-12-142009
2011-06-232011