Introduction:
The Lender Expression Engine provides robust support for handling dates, allowing users to perform various operations and comparisons. This article outlines the key date functions and operators available in the engine, along with examples to illustrate their usage.
Date Functions and Operators:
Creating Dates:
DATE('yyyy-MM-dd hh:mm', 'timezone')
Description: Creates a date object with the specified format and timezone.
Example:
DATE('2020-12-31', 'America/Chicago')
Comparison Operators:
!= (not equals):
Description: Checks if two dates are not equal.
Example:
DATE('2020-12-31', 'America/Chicago') != DATE('2020-01-01', 'America/Chicago')
> (greater than):
Description: Checks if one date is later than another.
Example:
DATE('2020-12-31 16:00:00', 'America/Chicago') > DATE('2020-12-31', 'America/Chicago')
< (less than):
Description: Checks if one date is earlier than another.
Example:
DATE('2020-12-31', 'America/Chicago') < DATE('2021-01-01', 'America/Chicago')
<= (less than or equal):
Description: Checks if one date is earlier than or equal to another.
Example:
DATE('2020-12-30', 'America/Chicago') <= DATE('2020-12-31', 'America/Chicago')
== (equals):
Description: Checks if two dates are equal.
Example:
Program.SomeDate == DATE('2020-12-31', 'America/Chicago')
>= (greater than or equal):
Description: Checks if one date is later than or equal to another.
Example:
DATE('2020-12-31', 'America/Chicago') >= DATE('2020-12-31', 'America/Chicago')
Specific Date Functions:
ADD_DAYS(date, countOfDays)
Description: Adds a specified number of days to a date and returns the new date.
Example:
ADD_DAYS(DATE('2020-01-01 16:00:00', 'America/Chicago'), 1) == DATE('2020-01-02 16:00:00', 'America/Chicago')
ADD_HOURS(date, countOfHours)
Description: Adds a specified number of hours to a date and returns the new date.
Example:
ADD_HOURS(DATE('2020-01-01 16:00:00', 'America/Chicago'), 1) == DATE('2020-01-01 17:00:00', 'America/Chicago')
ADD_MINUTES(date, countOfMinutes)
Description: Adds a specified number of minutes to a date and returns the new date.
Example:
ADD_MINUTES(DATE('2020-01-01 16:00:00', 'America/Chicago'), 1) == DATE('2020-01-01 16:01:00', 'America/Chicago')
ADD_MONTHS(date, countOfMonths)
Description: Adds a specified number of months to a date and returns the new date.
Example:
ADD_MONTHS(DATE('2020-01-01 16:00:00', 'America/Chicago'), 1) == DATE('2020-02-01 16:00:00', 'America/Chicago')
ADD_SECONDS(date, countOfSeconds)
Description: Adds a specified number of seconds to a date and returns the new date.
Example:
ADD_SECONDS(DATE('2020-01-01 16:00:00', 'America/Chicago'), 1) == DATE('2020-01-01 16:00:01', 'America/Chicago')
ADD_YEARS(date, countOfYears)
Description: Adds a specified number of years to a date and returns the new date.
Example:
ADD_YEARS(DATE('2020-01-01 16:00:00', 'America/Chicago'), 1) == DATE('2021-01-01 16:00:00', 'America/Chicago')
DATE_NOW(timeZone)
Description: Returns the current date and time for the specified timezone.
Example:
DATE_NOW('America/Chicago') == DATE('2020-12-22 16:00:00', 'America/Chicago')
GET_DATE(date)
Description: Returns the date part of a date-time value, discarding the time part.
Example:
GET_DATE(DATE('2020-12-31 16:00', 'America/Chicago')) == DATE('2020-12-31', 'America/Chicago')
GET_YEAR(date)
Description: Returns the year part of a date.
Example:
GET_YEAR(DATE('2020-12-31 16:00:00', 'America/Chicago')) == 2020
Examples of Date Function Usage:
Example 1: Adding Days to a Date
sqlCopy codeADD_DAYS(DATE('2021-01-01', 'America/Chicago'), 30) == DATE('2021-01-31', 'America/Chicago')This example adds 30 days to January 1, 2021, resulting in January 31, 2021.
Example 2: Checking Date Equality
sqlCopy codeProgram.SomeDate == DATE('2020-12-31', 'America/Chicago')This example checks if a program date is equal to December 31, 2020.
Example 3: Getting the Current Date and Time
scssCopy codeDATE_NOW('America/Chicago')This function returns the current date and time in the America/Chicago timezone.
Example 4: Extracting the Year from a Date
lessCopy codeGET_YEAR(DATE('2020-12-31', 'America/Chicago')) == 2020This example extracts the year from December 31, 2020, which is 2020.
Conclusion:
The Lender Expression Engine offers a comprehensive set of date functions that allow users to perform various operations and comparisons on dates. By using these functions, you can create dynamic and flexible workflows tailored to specific business needs.