Date Functions

Date functions perform operations and calculations on date and time values.

Date functions include:

ADD_MONTHS'(' start_date, num_months ')' :Returns the date that is num_months after start_date.
Inputs: A date and an integer.
Example:
```
ADD_MONTHS(CUSTOMERS.CUST_EFF_FROM,1)
```
LAST_DAY'(' value_returned_expression ')': Returns the last day of the month for the given date.
Inputs: An expression that evaluates to a date.
Example:
```
LAST_DAY(CUSTOMERS.CUST_EFF_FROM)
```
MONTHS_BETWEEN'(' end_date, start_date [, roundOff] ')': Returns the number of months between.
Inputs: Two expressions that evaluate to a date or timestamp and an optional BOOLEAN expression. The result is rounded to 8 digits unless roundOff=false.
Example:
```
MONTHS_BETWEEN(CUSTOMERS.CUST_EFF_FROM,CUSTOMERS.CUST_EFF_TO)
```
NEXT_DAY'(' value_returned_expression, day_of_week ')': Returns the first date after the expression that matches the specified day_of_week.
Inputs: A date and a string for the day of the week (Example: SU, SUN, SUNDAY).
Example:
```
NEXT_DAY(CUSTOMERS.CUST_EFF_FROM,'TUESDAY')
```
TRUNC'(' value_returned_expression, fmt ')': Truncates the date to the unit specified by the format fmt.
Inputs: A date and a format string (Example: year, yyyy, yy, mon, month, mm).
Example:
```
TRUNC(CUSTOMERS.CUST_EFF_FROM,'YYYY')
```
DATE_TRUNC'(' fmt, value_returned_expression ')': Truncates the timestamp to the unit specified by the format fmt.
Inputs: A timestamp and a format string (Example: YEAR, YYYY, YY, MON, MONTH, MM, DAY, DD, HOUR, MINUTE, SECOND, WEEK, QUARTER).
Example:
```
DATE_TRUNC('YEAR',CUSTOMERS.CUST_EFF_FROM)
```
DATE_SUB'(' start_date, integer ')': Returns the date obtained by subtracting the integer from the given date.
Inputs: An expression of date type and an integer.
Example:
```
DATE_SUB(CUSTOMERS.CUST_EFF_FROM,10)
```
DATEDIFF'(' end_date, start_date ')': Returns the number of days between start_date and end_date.
Inputs: Two dates.
Example:
```
DATEDIFF(CUSTOMERS.CUST_EFF_FROM,CUSTOMERS.CUST_EFF_TO)
```
DATE_ADD'(' date, integer ')': Returns the date obtained by adding the integer to the given date.
Inputs: A date and an integer.
Example:
```
DATE_ADD(CUSTOMERS.CUST_EFF_FROM,10)
```
DATE_FORMAT'(' timestamp, fmt ')': Converts the timestamp to a value of string in the format specified by the date format fmt.
Inputs: A timestamp and a format string. (Example: yyyy-MM-dd,yyyy.)
Example:
```
DATE_FORMAT(CUSTOMERS.CUST_EFF_FROM,'yyyy')
```
CURRENT_DATE '('')': Returns the current date.
CURRENT_TIMESTAMP '('')': Returns the current timestamp.
FROM_UNIXTIME' (' value_returned_expression [, fmt] ')': Converts UNIX time to timestamp.
Inputs: An integer expression representing UNIX time and an optional format string expression with a valid format.
Example:
```
FROM_UNIXTIME(10033743070)
```
UNIX_TIMESTAMP' (' value_returned_expression [, fmt] ')': Returns UNIX time.
Inputs: A date or timestamp, and an optional format string expression with a valid format (when the first input is of type string and not conformed to the default date or timestamp format).
Example:
```
UNIX_TIMESTAMP('2023-04-04 00:00:01', 'yyyy-MM-dd HH:mm:ss')
```