TalendDate routine - 7.3

Talend Open Studio User Guide
Version
7.3
Language
English
Product
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Module
Talend Studio
Content
Design and Development
Last publication date
2023-10-11
Available in...

Open Studio for Big Data

Open Studio for Data Integration

Open Studio for ESB

The TalendDate routine contains several functions which allow you to carry out different kinds of operations and checks concerning the format of Date expressions.

Warning: These functions internally use the Java class SimpleDateFormat except when the pattern is set to yyyy-MM-dd or yyyy-MM-dd HH:mm:ss, where there is no check for the format of the input date string in order to achieve better performance. You need to make sure the input date string matches the set pattern precisely when using either of these two patterns.

You can access these functions by double-clicking the TalendDate node under the system routines folder in the Repository tree view.

Function

Description

Syntax

addDate

Adds n days, n months, n hours, n minutes or n seconds to a Java date and returns the new date.

The Date format is: yyyy, MM, dd, HH, mm, ss or SSS.

TalendDate.addDate("initial date string", ["date format - eg.: yyyy/MM/dd"], integer n,"format of the part of the date to which n is to be added - eg.:yyyy").

compareDate

Compares all or part of two dates according to the format specified. Returns 0 if the dates are identical, -1 if the first date is earlier and 1 if the second date is earlier.

The pattern parameter specifies the date format, for example, "yyyy-mm-dd".

TalendDate.compareDate(Date date1, Date date2,[String pattern])

diffDate

Returns the difference between two dates in terms of years, months, days, hours, minutes, seconds, or milliseconds according to the comparison parameter specified.

The dateType parameter specifies the date part to be compared. The valid values are: "yyyy", "MM", "dd", "HH", "mm", "ss", "SSS". The default value is "dd".

The ignoreDST parameter specifies if DST needs to be taken into consideration.

TalendDate.diffDate(Date date1, Date date2,[String dateType],[boolean ignoreDST])

For example, TalendDate.diffDate(TalendDate.parseDate("yyyy-MM-dd", "2022-12-01"), TalendDate.parseDate("yyyy-MM-dd", "2022-10-02"), "MM") returns 2.

diffDateFloor

Returns the difference between two dates in terms of years or months according to the comparison parameter specified. The value is rounded down to the nearest integer.

The dateType parameter specifies the date part to be compared. The valid values are: "yyyy", "MM".

TalendDate.diffDateFloor(Date date1, Date date2, String dateType)

For example, TalendDate.diffDateFloor(TalendDate.parseDate("yyyy-MM-dd", "2022-12-01"), TalendDate.parseDate("yyyy-MM-dd", "2022-10-02"), "MM") returns 1.
diffDateIgnoreDST

Returns the difference between two dates in terms of days, months or years according to the comparison parameter specified, ignoring the DST.

The dateType parameter specifies the format of the date part to be compared, for example, "yyyy".

TalendDate.diffDateIgnoreDST(Date1(), Date2(),[String dateType])

formatDate

Returns a date string which corresponds to the format specified.

TalendDate.formatDate("date format - eg.: yyyy-MM-dd HH:mm:ss", Date() to be formatted)

formatDateInTimeZone

Formats a date into the specified format date/time string under the given timezone.

TalendDate.formatDateInTimeZone(String datePattern, Date tate, String TimeZoneID)

formatDateInUTC

Formats a date into the specified format date/time string under the UTC timezone.

TalendDate.formatDateInUTC(String datePattern, Date tate, String TimeZoneID)

formatDateLocale

Changes a date into a date/hour string according to the format used in the target country.

TalendDate.formatDateLocale("format target", java.util.Date date, "language or country code")

formatDatetimeoffset

Formats date to MSSQL 2008 type datetimeoffset ISO 8601 string with local time zone format string: yyyy-MM-dd HH:mm:ss.SSSXXX (JDK7 support it).

TalendDate.formatDatetimeoffset(Date date)

getCurrentDate

Returns the current date. No entry parameter is required.

TalendDate.getCurrentDate()

getDate

Returns the current date and hour in the format specified (optional). This string can contain fixed character strings or variables linked to the date. By default, the string is returned in the format of DD/MM/CCYY.

TalendDate.getDate("Format of the string - ex: CCYY-MM-DD")

getFirstDayOfMonth

Changes the date of an event to the first day of the current month and returns the new date.

TalendDate.getFirstDayMonth(Date)

getLastDayOfMonth

Changes the date of an event to the last day of the current month and returns the new date.

TalendDate.getLastDayMonth(Date)

getPartOfDate

Returns part of a date according to the format specified. This string can contain fixed character strings or variables linked to the date.

TalendDate.getPartOfDate("String indicating the part of the date to be retrieved, "String in the format of the date to be parsed")

getRandomDate

Returns a random date, in the ISO format.

TalendDate.getRandomDate("format date of the character string", String minDate, String maxDate)

isDate

Checks whether the date string corresponds to the format specified. Returns the boolean value true or false according to the outcome.

TalendDate.isDate(Date() to be checked, String pattern, [boolean ignoreTimeZone])

where, pattern specifies the format of the date to be checked. For example, yyyy-MM-dd HH:mm:ss.

isDateStrict

Tests string value as a date with right pattern using strict rules. This validation uses Java 8 time tools. The range of time-zone offsets is restricted to -18:00 to 18:00 inclusive. Returns a boolean value indicating whether the stringDate is a date string with a right pattern.

TalendDate.isDateStrict(String stringDate, String pattern)

parseDate

Changes a string into a Date. Returns a date in the specified format.

TalendDate.parseDate("format date of the string to be parsed", "string in the format of the date to be parsed",["boolean about whether parsing is set to be lenient, that is to say, accepting the heuristic match with the format"])

parseDateInUTC

changes a string into a Date in UTC. Returns a date in the UTC format.

TalendDate.parseDateInUTC("format date of the string to be parsed", "string in the format of the date to be parsed", ["boolean about whether parsing is set to be lenient, that is to say, accepting the heuristic match with the format"])

parseDateLocale

Parses a string according to a specified format and extracts the date. Returns the date according to the local format specified.

TalendDate.parseDateLocale("date format of the string to be parsed", "String in the format of the date to be parsed", "code corresponding to the country or language")

setDate

Modifies part of a date according to the part and value of the date specified and the format specified.

TalendDate.setDate(Date, whole n, "format of the part of the date to be modified - eg.:yyyy")

TO_CHAR

Converts a date to a character string.

TalendDate.TO_CHAR(date[,format])

  • date: the date value you want to convert to a character string.
  • format: the string which defines the format of the return value.

TO_DATE

Converts a character string to a Date/Time datatype.

TalendDate.TO_DATE(string[, format])

  • string: the string you want to convert to a Date/Time datatype.
  • format: the format string that matches the part of the string argument. If not specified, the string value must be in the date format MM/dd/yyyy HH:mm:ss.SSS.

For example, TalendDate.TO_DATE("04/24/2017 13:55:42.123") will return Mon Apr 24 13:55:42 CST 2017.

ADD_TO_DATE

Adds a specified amount to one part of a datetime value, and returns a date in the same format as the date you pass to the function.

TalendDate.ADD_TO_DATE(date, format, amount)

  • date: the date value you want to change.
  • format: the format string specifying the portion of the date value you want to change.
    • Valid format strings for year: Y, YY, YYY, and YYYY.
    • Valid format strings for month: MONTH, MM, and MON.
    • Valid format strings for day: D, DD, DDD, DAY, and DY.
    • Valid format strings for hour: HH, HH12, and HH24.
    • Valid format string for minute: MI.
    • Valid format string for second: SS.
    • Valid format string for millisecond: MS.
  • amount: the integer value specifying the amount of years, months, days, hours, and so on by which you want to change the date value.

For example,

if TalendDate.getCurrentDate() returns Mon Apr 24 14:26:03 CST 2017,

TalendDate.ADD_TO_DATE(TalendDate.getCurrentDate(), "YY", 1) will return Tue Apr 24 14:26:03 CST 2018.
Warning:

Although "yyyy" and "YYYY" in the date format return the same year number in most cases, "YYYY" may not work as expected when used:

  • at the first week of a year if the year does not start by the first day of the week.
  • at the last week of a year if the year does not end by the last day of the week.

For example, when calculating what date it is 3 days before January 2, 2016, the code below would return a wrong date:

System.out.println(TalendDate.formatDate("YYYY-MM-dd", TalendDate.addDate(TalendDate.TO_DATE("01/02/2016 08:10:30.123"), -3, "dd")));

while the following code would work as expected:

System.out.println(TalendDate.formatDate("yyyy-MM-dd", TalendDate.addDate(TalendDate.TO_DATE("01/02/2016 08:10:30.123"), -3, "dd")));

Therefore, you should typically use "yyyy", which represents calendar year.