Date Functions

Home \| Table of Contents	Date Functions
Prev	Functions Reference	Next

When you work with date, you may use the functions that process dates.

In these functions, sometimes a format pattern of a date or any number must be defined. Also locale can have an influence to their formatting.

For detailed information about date formatting and/or parsing see Data and Time Format.
For detailed information about locale see Locale.

Note

	Note
Remember that numeric and date formats are displayed using system value Locale or Locale specified in the `defaultProperties` file, unless other Locale is explicitly specified. For more information on how Locale may be changed in the `defaultProperties` see Changing Default CloverETL Settings.

Remember that numeric and date formats are displayed using system value Locale or Locale specified in the defaultProperties file, unless other Locale is explicitly specified.

For more information on how Locale may be changed in the defaultProperties see Changing Default CloverETL Settings.

Here we provide the list of the functions:

date dateAdd(date arg, long amount, unit timeunit);
The dateAdd(date, long, unit) function accepts three arguments: the first is date, the second is of long data type and the last is any time unit. The unit can be one of the following: year, month, week, day, hour, minute, second, millisec. The unit must be specified as a constant. It can neither be received through an edge nor set as variable. The function takes the first argument, adds the amount of time units to it and returns the result as a date. The amount and time unit are specified as the second and third arguments, respectively.

long dateDiff(date later, date earlier, unit timeunit);
The dateDiff(date, date, unit) function accepts three arguments: two dates and one time unit. It takes these arguments and subtracts the second argument from the first argument. The unit can be one of the following: year, month, week, day, hour, minute, second, millisec. The unit must be specified as a constant. It can be neither received through an edge nor set as variable. The function returns the resulting time difference expressed in time units specified as the third argument. Thus, the difference of two dates is expressed in defined time units. The result is expressed as an integer number. Thus, dateDiff(2008-06-18, 2001-02-03, year) returns 7. But, dateDiff(2001-02-03, 2008-06-18, year) returns -7!

date extractDate(date arg);
The extractDate(date) function takes one date argument and returns only the information containing year, month, and day values. The function's argument is not modified by the return value.

date extractTime(date arg);
The extractTime(date) function takes one date argument and returns only the information containing hours, minutes, seconds, and milliseconds. The function's argument is not modified by the return value.

date randomDate(date startDate, date endDate);
The randomDate(date, date) function accepts two date arguments and returns a random date between startDate and endDate. These resulting dates are generated at random for different records and different fields. They can be different for both records and fields. The return value can also be startDate or endDate. However, it cannot be the date before startDate nor after endDate. Remember that dates represent 0 hours and 0 minutes and 0 seconds and 0 milliseconds of the specified day, thus, if you want that endDate could be returned, enter the next date as endDate. As locale, system value is used. The default format is specified in the defaultProperties file.

date randomDate(long startDate, long endDate);
The randomDate(long, long) function accepts two arguments of long data type - each of them represents a date - and returns a random date between startDate and endDate. These resulting dates are generated at random for different records and different fields. They can be different for both records and fields. The return value can also be startDate or endDate. However, it cannot be the date before startDate nor after endDate. Remember that dates represent 0 hours and 0 minutes and 0 seconds and 0 milliseconds of the specified day, thus, if you want that endDate could be returned, enter the next date as endDate. As locale, system value is used. The default format is specified in the defaultProperties file.

date randomDate(string startDate, string endDate, string format);
The randomDate(string, string, string) function accepts three stringarguments. Two first represent dates, the third represents a format. The function returns a random date between startDate and endDate corresponding to the format specified by the third argument. These resulting dates are generated at random for different records and different fields. They can be different for both records and fields. The return value can also be startDate or endDate. However, it cannot be the date before startDate nor after endDate. Remember that dates represent 0 hours and 0 minutes and 0 seconds and 0 milliseconds of the specified day, thus, if you want that endDate could be returned, enter the next date as endDate. As locale, system value is used.

date randomDate(string startDate, string endDate, string format, string locale);
The randomDate(string, string, string, string) function accepts four string arguments. The first and the second argument represent dates. The third is a format and the fourt is locale. The function returns a random date between startDate and endDate. These resulting dates are generated at random for different records and different fields. They can be different for both records and fields. The return value can also be startDate or endDate corresponding to the format and the locale specified by the third and the fourth argument, respectively. However, it cannot be the date before startDate nor after endDate. Remember that dates represent 0 hours and 0 minutes and 0 seconds and 0 milliseconds of the specified day, thus, if you want that endDate could be returned, enter the next date as endDate.

date today();
The today() function accepts no argument and returns current date and time.

date zeroDate();
The zeroDate() function accepts no argument and returns 1.1.1970.

Note

	Note
The following two functions are deprecated. Their return value modifies the argument at the same time. `date trunc(`date `arg)`; The `trunc(date)` function takes one date argument and returns the date with the same year, month and day, but hour, minute, second and millisecond are set to 0 values. `date truncDate(`date `arg)`; The `truncDate(date)` function takes one date argument and returns the date with the same hour, minute, second and millisecond, but year, month and day are set to 0 values. The 0 date is 0001-01-01.

The following two functions are deprecated. Their return value modifies the argument at the same time.

date trunc(date arg);
The trunc(date) function takes one date argument and returns the date with the same year, month and day, but hour, minute, second and millisecond are set to 0 values.

date truncDate(date arg);
The truncDate(date) function takes one date argument and returns the date with the same hour, minute, second and millisecond, but year, month and day are set to 0 values. The 0 date is 0001-01-01.

Prev	Up	Next
Conversion Functions	Home \| Table of Contents	Mathematical Functions