Date Functions
Date Functions
| Function | Description |
|---|---|
| absMonth(Date dt) | Returns the number of months since AD. |
| absQuarter(Date dt) | Returns the number of quarters since AD as an integer value. |
| amPm(Date dt) | Returns Am or Pm value for an input Date as a String. |
| Date(int year, int month, int date) | Creates a date using input numbers for year, month, and date, for example, Date(2018, 7, 12) -> 12 July 2018 (00:00:00.000). |
| dateDif(startDate, endDate, unit) | Calculates the difference between dates in days, months, and years. This function accepts the following values: - D Calculates difference in full days. For example, for dateDif(toDate(“2011-02-01”),toDate(“2012-05-01”),”D”), the result is 455 days. - M Calculates difference in full month. Note that for dateDif(toDate(“2016-08-31”),toDate(“2016-09-30”),”M”), the result is 0 because dateDif is rounded down by default. Note: If your business case requires that difference is 1, use an alternative formula is (Year(date1)-Year(date2))*12+Month(date1)-Month(date2). - Y Calculates difference in full years. For example, dateDif(toDate(“2020-03-04”),toDate(“2027-05-01”),”Y”), the result is 7. - W Calculates difference in full weeks. - MD Calculates difference in full days excluding months and years. For example, for dateDif(toDate(“2011-02-14”),toDate(“2012-05-14”),”MD”), the result is 0 as 14 is compared to 14. - YD Calculates difference in full days excluding years. - YM Calculates difference in full months excluding years. - MF Calculates difference in month. A fractional result can be returned if the last month is not completed. For example, for dateDif(toDate(“2020-03-04”),toDate(“2027-05-01”),”MF”), the result is 85.9. - YF Calculates difference in years. A fractional result can be returned if the last year is not completed. - WF Calculates difference in weeks. A fractional result can be returned if the last week is not completed. - YMF Calculates difference in month excluding years. A fractional result can be returned if the last month is not completed. |
| dateToString(Date dt) | Converts a date to the String. Deprecated, use toString(Date dt) function instead. |
| dateToString(Date dt, String dateFormat) | Converts a date to the String according dateFormat. Deprecated, use toDate (String str, String dateFormat) function instead. |
| dayDiff(Date dt1, Date dt2) | Returns the difference in days between endDate and startDate. |
| dayOfMonth(Date dt) | Returns the day of month. |
| dayOfWeek(Date dt) | Returns the day of week. |
| dayOfYear(Date dt) | Returns the day of year. |
| firstDateOfQuarter(int absQuarter) | Returns the first date of quarter. |
| hour(Date dt) | Returns the hour. |
| hourOfDay(Date dt) | Returns the hour of day. |
| lastDateOfQuarter(int absQuarter) | Returns the last date of the quarter. |
| lastDayOfMonth(Date dt) | Returns the last date of the month. |
| minute(Date dt) | Returns the minute. |
| month(Date dt) | Returns the month (1 to 12) of an input date. |
| monthDiff(Date dt1, Date dt2) | Return the difference in months before d1 and d2. |
| quarter(Date dt) | Returns the quarter (0 to 3) of an input date. |
| second(Date dt) | Returns the second (0-59) of an input date. |
| toDate(String str) | Converts a string to a date. Date in str must be represented as text in the supported by OpenL format as described in Representing Date Values. For example toDate(“1980-07-12”) -> 12 July 1980 (00:00:00.000). |
| toDate(String str, String dateFormat) | Converts a string into the date of the specified format. For date formats, see https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/text/SimpleDateFormat.html. |
| toString(Date dt) | Converts a date to the string in MM/dd/yyyy format. |
| toString(Date dt, String dateFormat) | Converts a date to the string of the specified format. For date formats, see https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/text/SimpleDateFormat.html. |
| weekDiff(Date dt1, Date dt2) | Returns the difference in weeks between endDate and startDate. |
| weekOfMonth(Date dt) | Returns the week of the month within which that date is. |
| weekOfYear(Date dt) | Returns the week of the year on which that date falls. |
| yearDiff(Date dt1, Date dt2) | Returns the difference in years between endDate and startDate. |
| year(Date dt) | Returns the year for an input date. For example, for the 2/1/2011 input, the function returns 2011. |