Many Jitterbit functions are available through Jitterbit Studio and Jitterbit Harmony Cloud Studio, some of function are designed specifically for working with conversions relating to DateTime, Time Zone, etc. The implementation of these DateTime methods is supported by Jitterbit Studio and Jitterbit Cloud Studio.
Also Read, How to Download Jitterbit Studio in Windows, Linux, and Mac.
Although DataTime methods are inherently used by Jitterbit scripts, no datetime functions have been developed specifically for Jitterbit Javascript to carry out datetime-related tasks. If you are knowledgeable about JavaScript, though, you might find a different approach.
| Symbol | Logic | Reference |
|---|---|---|
| yyyy | 4 digits (Year) | 2022 |
| ??yy | 2 or 4 digits (Year) | 2022 or 22 |
| yy | Last 2 digits (Year) | 2022 is equivalent to (22) |
| mmm | In 3 character (Month) | Jan, Feb, Mar, ....., Dec |
| mm | 2 digits (Month) | 01 to 12 |
| ?m | 1 or 2 digits (Month) | 1 to 12 |
| dd | 2 digits (Date) | 01 to 30 |
| ?d | 1 or 2 digits (Date) | 1 to 30 |
| ddd | Julian (Date) | (001 to 366) |
| HH | 00 -23 (Hours) | 20:30 is equivalent to (8:30PM) |
| HH | 01 -12 (Hours, if AP mention) | 8:30PM / 03:00AM |
| MM | 00 - 59 (Minutes) | 00 - 59 |
| SS | 00 - 59 (Seconds) | 00 - 59 |
| zzz | MilliSeconds (000 -999) | 000 -999 |
| AP | AM/PM | 8:00AM/ 6:00PM |
Date and Time Functions in Jitterbit Studio
1. CVTDate():
This Function is used to convert Timestamps into any Valid Format, it
depends on us how we want to extract the Date from this Function. It is
naturally very flexible to extract the year, month, date, hours, minutes,
and seconds separately.
CVTDate(<inputDate>,<oldFormat>,<newFormat>);
CVTDate("11/24/2022 11:40:25 AM","mm/dd/yyyy HH:MM:SS" , "mm/dd/yyyy");
//Return: 11/24/2022
CVTDate("1/4/2022 11:40:25 AM","?m/?d/yyyy HH:MM:SS" , "dd-mm-yyyy");
//Return: 04-01-2022
CVTDate("1/4/2022 01:40:25 AM","?m/?d/yyyy HH:MM:SS AP" , "dd-mm-yyyy HH-MM-SS AP")
//Return: 04-01-2022 01-40-25 AM
CVTDate("1/4/2022", "?m/?d/yyyy", "dd-mm-yyyy HH-MM-SS AP")
//Return: 04-01-2022 12-00-00 AM
CVTDate("30122020", "ddmmyyyy", "yyyy/mm/dd HH-MM-SS AP")
//Return: 2020/12/30 12-00-00 AM
CVTDate("30122020233050", "ddmmyyyyHHMMSS", "yyyy/mm/dd HH:MM:SS AP");
//Return: 2020/12/30 11:30:50 PM
2. ConvertTimeZone():
This function is used to change the time into local standard time. If we are
developing an application for a different timezone then the time conversion
should be managed according to the local timezone. If the client system
requests to store the time in another format then this function does this
conversion.
Ex- UTC to PST, UMT to IST.
ConvertTimeZone(<date>, <fromTZ>, <toTZ>[, <is_european_format>, <ignoreDST>]);
ConvertTimeZone("11/24/2022 11:30:25 AM","UTC","IST");
//Return: 2022-11-24 17:00:25
ConvertTimeZone("11/24/2022 17:30:25","UTC","IST")
//Return: 2022-11-24 23:00:25
ConvertTimeZone("11/24/2022 17:30:25","UTC","EST")
//Return: 2022-11-24 12:30:25
3. DayOfMonth():
This function returns an one or two-digit "Date" of the month. It extracts the date from the timestamp only if the given date is in a valid format otherwise, an error may occur. Jitterbit has a another function "Eval()" which is more useful for handling any datetime conversion error.
DayOfMonth(<date>);
DayOfMonth("8/29/2022 11:36:00 AM")
//Return 29
DayOfMonth(Now());
//Return 8, if today's date is "08-Aug-2008"
DayOfMonth("14-Nov-1997")
//Return 14
Eval(DayOfMonth("18/09/2022"),"Date Time Format not Valid.")
// Return: Date Format not Valid.
//Note: In this conversion, the script will not fail as the Eval() function has been used here to handle the exception condition. Also, you can assume that the 'Eval()' function follows the functionality of Try - Catch error handling.
4. DateAdd():
This function is used to obtain the future date following the addition of the necessary digit. Changes can also be made in Hours, Minutes, and Seconds. Simply input a negative number to get the date from last year or last month; on the other hand, a positive number will give you the date in the future.
| Property | Year | Month | Date | Hour | Minute | Second | Millisecond |
|---|---|---|---|---|---|---|---|
| Format | yyyy | mm | dd | hh | mi | ss | zzz |
Alert:
This table is valid for 'DateAdd()' function only. Kindly refer
main Format Table for other functions.
DateAdd(<datepart>, <number>, <date>);
DateAdd('dd',2,Now());
//Return: 2022-12-06 19:17:18
DateAdd('yyyy',2,Now());
//Return: 2024-12-04 19:20:10
DateAdd('mm',2,"8/19/2022");
//Return: 2024-10-19
Eval(DateAdd('mm',2,"25/8/2022"),"DateTime Format not Valid" );
//Return: DateTime Format not Valid
DateAdd('mm',2,"8/25/2022");
//Return: 2024-10-19
DateAdd('mi',14,"8/29/2022 11:36:00 AM");
//Return: 2022-08-29 11:50:00
DateAdd('mi',-16,"8/29/2022 11:36:00 AM" );
//2022-08-29 11:20:00
DateAdd('dd',-14,"08/29/2022 11:36:00 AM" );
//2022-08-15 11:36:00
DateAdd('yyyy',2,Now());
//Return: 2022-08-05 16:19:50 , if today's year is 2020
5. DayOfWeek():
The output of this function runs from 0 to 6, representing the days of the week from "Sunday" through "Saturday." The timestamp is used to extract the day of the week if the given date is in an appropriate format, otherwise, an error may occur. Eval(), a different function in Jitterbit Studio and Jitterbit Harmony Cloud Studio, is more suited for handling incorrect date-to-time conversions.
DayOfWeek(<date>);
DayOfWeek("8/29/2022 11:36:00 AM")
//Return: 1, which equivalent to 'Monday'
DayOfWeek(Now());
//Return: 5, which equivalent to 'Thursday' if today's date is "07-Aug-2008"
DayOfWeek("14-Nov-1997")
//Return: 6, which equivalent to 'Friday'
Eval(DayOfWeek("18/09/2022"),"Date Format not Valid.")
// Return: Date Time Format not Valid.
Note: In this conversion, the script will not fail as the 'Eval()' function has been used here to handle the exception condition. Also, you can assume that the Eval() function follows the functionality of Try - Catch error handling.
6. GeneralDate():
The timestamp format displayed by this function is default or predetermined. It is required to provide the date, but if you do not specify a time, the default time will be "12:00:00 AM."
GeneralDate(<date>);
GeneralDate('2024-12-14 20:20:10');
//Result: 12/14/2024 08:20:10 PM
CVTDate("11-24-2016 11:40:25 AM","mm-dd-yyyy HH:MM:SS" , "GeneralDate");
//Result: 11/24/2016 11:40:25 AM
GeneralDate("08-15-1947" );
//Result: 08/15/1947 12:00:00 AM
GeneralDate("12-13-14" );
//Result: 12/13/2014 12:00:00 AM
Eval(CVTDate(GeneralDate("12-12-2014" ),"mm-dd-yyyy HH:MM:SS",'ddd'),"DateTime Format not Valid");
//Result: 346.
//It displays the current date in Julian format. Additionally, if an expression error occurs, a manual error message will print instead of the script failing.
7. FormatDate():
This function prettifies the timestamp in the desired format; it is functionally comparable to the "CVDate()" function but is less flexible when performing complex conversions than the "CVDate()" function is. When we are certain of the date format for the input, we may utilize this function. In contrast to the 'CVDate()' method. Although this function does not have an additional parameter for specifying the input Date Format, but it is nevertheless more useful in many circumstances. See the illustrations below.
| Timestamp Property | %X | %x | %c | %A | %w | %W | %b | %B | %a | %p | %I | %H | %Z |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Output Format | Hour (24-hour clock) 23:30:00 |
08/16/1988 | Mon Nov 21 11:40:25 2016 | Sunday | 0 to 6 | 0 to 53 | Jan | January | Mon | AM/PM | Hour (12-hour clock) | Hour (24-hour clock) | UTC,PST,IST,etc. |
| Description | Extract the Time Stamp. | Extract the Date. | Default Time Format. | Weekday full name. | Day of the week. | Week of the year. | Month abbreviated name. | Month full name. | Weekday abbreviated name. | Equivalent to AM/PM. | Extract only Hour | Extract only Hour | Rreturn Time zone name |
FormatDate(<date>, <format>);
FormatDate("11-21-2016 11:40:25 AM", "mm-dd-yyyy");
// 11-21-2016
FormatDate("11-21-2016 11:40:25:555 AM", "yyyy.mm.dd HH:MM:SS:zzz");
//2016.11.21 11:40:25:555
FormatDate("11-21-2016 11:40:25 AM", '%X');
//11:40:25 , Only extract the time
FormatDate("11-21-2016 11:40:25 AM", '%x');
//11/21/16 , Only extract the Date
FormatDate("11-21-2016 11:40:25 AM", '%c');
//Mon Nov 21 11:40:25 2016, Another way of Date and Time representation.
FormatDate("11-21-2016 11:40:25 AM", '%A');
//Return: Monday, (day name of the week.)
FormatDate("11-21-2016 11:40:25 AM", '%w');
//Return: 1, (day of the week.)
FormatDate("5-2-2023 11:40:25 AM", '%W');
//Return: 47, (Week number Of the year.)
FormatDate("11-21-2016 11:40:25 AM", '%b');
//Return: Nov, (Month as locale’s abbreviated name. Jan,Feb...,Dec)
FormatDate("11-21-2016 11:40:25 AM", '%B');
//Return: November, (Month as full name.)
FormatDate( "11/15/2022" , 'LongDate')
//Tuesday, November 15, 2022
FormatDate("11-21-2016 11:40:25 AM", '%a');
//Return: Mon, (Short week name.)
FormatDate("11-21-10 11:40:25 PM", '%p');
//Return: PM, (equivalent of either AM or PM.)
FormatDate("11-21-20 10:40:25 PM", '%I');
//Return: 10 , [Hour (12-hour clock)]
FormatDate("11-21-2012 11:40:25 PM", '%H');
//Return: 23, [Hour (24-hour clock)]
FormatDate("11-21-2016 11:40:25 AM", '%Z');
//Return: Coordinated Universal Time,
//Note: The returned Time Zone name based on the Jitterbit Cloud or Jitterbit Private Agent Installation machine.
8. GetUTCFormattedDate():
The "GetUTCFormattedDate()" function formats the entire DateTime and only returns the Date in UTC format. You can enter the timestamp in any format, including IST, PST, CST, MST, EST and etc.
Note: The output of this function is always in UTC.
GetUTCFormattedDate(<input_date>, <time_zone_id>, <is_european_format>);
GetUTCFormattedDate("12/15/2022 22:38:34", "IST",false);
//Return: 2022-12-15
GetUTCFormattedDate("12/19/2022", "EST",false);
//Return: 2022-12-19
GetUTCFormattedDate("17/11/2022 14:38:34", "PST",true);
//Return: 2022-11-17
FormatDate(GetUTCFormattedDate("17/11/2022 14:38:34", "PST",true), 'yyyy/mm/dd HH:MM:SS');
//Return: 2022/11/17 00:00:00
//Note: The time will be returned as 12AM or 00 Hours because the GetFormattedDate() function only returns Date and the Time parameter always be null.
9. GetUTCFormattedDateTime():
The "GetUTCFormattedDateTime()" function formats the entire date and time in UTC. Any time zone, including IST, PST, CST, MST, and EST, can be entered and converted to UTC time.
Note: The output of this function is always in UTC.
GetUTCFormattedDateTime(<input_date>, <time_zone_id>, <is_european_format>);
GetUTCFormattedDateTime(Now(), "IST", false);
// Result: 2022-12-08T12:25:50Z
// Date format should be 'dd/mm/yyyy' if <is_european_format> True
dateTime= GetUTCFormattedDateTime("15/12/2022 1:38:34 PM", "PST",true);
FormatDate(dateTime,'yyyy.mm.dd HH:MM:SS AP');
//Return: 2022.12.15 09:38:34 AM, [it will convert PST time and give result in UTC format.]
dateTime= GetUTCFormattedDateTime("12/15/2021 11:30:34 AM", "IST",false);
FormatDate(dateTime,'yyyy.mm.dd HH:MM:SS AP')
//Return: 2021.12.15 06:00:34 AM, [it will convert IST time and give result in UTC format.]
// Date format should be 'mm/dd/yyyy' if <is_european_format> False
dateTime= GetUTCFormattedDateTime("12/15/2022 1:38:34 PM", "PST",false);
FormatDate(dateTime,'yyyy.mm.dd HH:MM:SS AP')
//Return: 2022.12.15 09:38:34 AM, [it will convert PST time and give result in UTC format.]
dateTime='20221215133010'; // YearMonthDateHourMinuteSecond
dateTime=CVTDate(dateTime, 'yyyymmddHHMMSS', 'mm/dd/yyyy HH:MM:SS');
dateTime= GetUTCFormattedDateTime(dateTime, "PST",false);
FormatDate(dateTime,'yyyy.mm.dd HH:MM:SS AP');
//Return: 2022.12.15 09:38:34 AM
10. LastDayOfMonth():
The LastDayOfMonth() function returns the 'Date' of the month's end without the Timestamp and only accepts one parameter as an input date.
LastDayOfMonth(<date>);
LastDayOfMonth("11/15/2022");
//2022-11-30
11. LongDate():
The LongDate() function only accepts one parameter as an input date and returns the "Date" with the weeks name excluding the Timestamp.
LongDate(<date>);
LongDate(Now());
//Return: Thursday, December 08, 2022
LongDate("11/15/2022 14:38:34");
//Return: Tuesday, November 15, 2022
FormatDate( LongDate("11/15/2022 14:38:34") , 'GeneralDate');
//Return: 11/15/2022 12:00:00 AM,
//Note: [ Because the LongDate method doesn't produce a timestamp, the time is always set to the default of 12AM or 00H. ]
FormatDate( "11/15/2022" , 'LongDate');
//Return: Tuesday, November 15, 2022
12. LongTime():
The LongTime() function only accepts one parameter as an input date and returns the "Time" excluding the "Date".
LongTime(<date>);
LongTime(Now());
//Return: 05:41:00 PM, if today's timestamp is :2021-06-18 17:42:06
LongTime( "06/15/2023 22:38:34" );
//Return: 10:38:34 PM
LongTime( "06/15/2023 12:38:34 PM" );
//Return: 12:38:34 PM
13. MediumDate():
The MediumDate() function returns the "Date" with a two-digit year, a three-character month name and a two-digit date excluding the "Time." It only accepts one paramenter as a date input.
MediumDate(<date>);
MediumDate(Now());
//Return: 14-Aug-21
MediumDate(GetUTCFormattedDateTime("15/11/2022 01:38:34", 'IST', true));
//Return: 14-Nov-22
FormatDate(MediumDate(Now()) , 'dd-mmm-yyyy');
//Return: 08-Dec-2022
14. MonthOfYear():
The 'month' of the year is returned as an integer between 1 and 12 by the MonthOfYear() function. When the month is taken from the timestamp, this function will be useful.
MonthOfYear(<date>);
MonthOfYear(Now());
//Return: 3, which equivalentto 'March' if today's timestamp is :2021-03-18 17:42:06
MonthOfYear( "10/15/2023 22:38:34" );
//Return: 10
MonthOfYear( "06/15/2023 12:38:34 PM" );
//Return: 6
15. MediumTime():
The MediumTime() function only accepts one parameter as an input date and returns the "Time" without the "Date". Note: The only real distinction between the LongTime() and MediumTime() functions is that LongTime() includes seconds in the timestamp while MediumTime() does not.
MediumTime(<date>);
MediumTime(Now());
//Return: 05:41 PM, if today's timestamp is :2021-06-18 17:42:06
MediumTime( "06/15/2023 22:38:34" );
//Return: 10:38 PM
MediumTime( "06/15/2023 12:38:34 PM" );
//Return: 12:38 PM
16. Now():
The Now() function returns a timestamp, date, and time, excluding microseconds. This function generates local timestamps based on the region in which the Jitterbit Agent is installed. CVTDate() and FormatDate() can be used to format the Now() function's default date format ().
Now();
Now();
//Return: 2023-05-28 15:19:16
FormatDate(Now(),'HH:MM:SS');
//Return: 15:30:35
FormatDate(Now(),"Today's Date is: %x, And Current Time is: %X %p");
//Return: Today's Date is: 06/15/22, And Current Time is: 10:01:04 AM
17. ShortDate():
The ShortDate() function only returns a date(month/date/year); it does not return time information, and the year is always formatted as two digits. When it's necessary to extract the date from the source date, this function comes in handy.
ShortDate(<date>);
ShortDate(Now());
//Return: 10/25/23, [If current date is 10/25/2023]
ShortDate('2023-06-19 00:18:32.474');
//Return: 06/19/23
18. Now_():
Similar to Jitterbit's Now() method, the Now_() function returns a comprehensive timestamp with information at the microsecond level. Most usable while tracking the service consuming time or tracking the component execution time.
Now_();
CVTDate(Now_() ,"GeneralDate", "dd-mm-yyyy HH:MM:SS zzz AP");
//Return: 31-02-2023 12:17:13 919 AM // 919 is pointing the microseconds.
Now_();
//Return: 2023-02-31 15:11:09.786
19. ShortTime():
The Jitterbit ShortTime() function just return a time (hours:minutes) without the seconds; it does not return the date information. When capturing only time information is required, it might be useful.
ShortTime("date");
ShortTime(Now());
//Return: 15:20 // return current time, it depends on the region where the Jitterbit AGENT has running.
ShortTime("10/15/2023");
//Return: 00:00 // Time is not define, so the result always be shown as 00:00.
ShortTime("10/15/2023 20:13:40");
//Return: 20:13
20. Eval():
The Jitterbit Eval() function can be used to test an expression or condition, and if the test fails for any reason, the failure statement will be executed and provide more information about the error. It can be used for date-time conversion functions, running database scripts or calling operation from project as well.
Eval(<exp_to_evaluate>,<default_result>)
Eval(CVTDate(Now(), 'dd/mm/yyyy', 'GeneralDate'), "DateTime Conversion Failed");
//Return: 12/15/2023 12:00:00 AM
Eval(CVTDate('155/12/2023', 'dd/mm/yyyy', 'GeneralDate'), "Else part is running now:- Wrong Format DateTime Conversion Failed");
// Return : Else part is running now:- Wrong Format DateTime Conversion Failed
createdate = "1478119530707";
// You can convert it to a date by truncating the last three digits and using FormatDate:
date = double(Left(createdate, 10));
FormatDate(date, "yyyy-mm-dd HH:MM:SS");
