Use of SYSDATE
User wants to convert SYSDATE to the current Time Zone based from the User’s Set Preferences Time Zone.
Extracting results via a Case Saved Search will return the list of results within last 30 mins. (i.e., Last modified date of a record to SYSDATE).
However, SYSDATE returns the server date which requires it to be manually converted to the user’s time zone. TO_CHAR() is not ideal when it comes to time zone conversion.
An example is adding the use of the SQL expression SYSDATE in a saved time search. User adds the comment explaining the function of the SQL expression SYSDATE which returns the current date and time set.
Add a Comment in the Formula of a Time Saved Search
Set the date/time the transaction was approved based on the approver’s time zone on a free-form text field.
The sysdate in TO_CHAR(SYSDATE, ‘YYYY/MM/DD’) will return NetSuite server’s time which is in PST.
The transaction was created before a specific time.
In this specific formula, transactions created before 11:00 AM will appear.
CASE WHEN {datecreated} < TRUNC(sysdate) + 11/24 THEN 1 ELSE 0 END
convert the SYSDATE to a different time zone. TO_CHAR(SYSDATE – x/24, ‘date_format time_format ” optional_string”‘)
Note: Use the following reference to identify what should be modified on the formula.
SYSDATE – Current Date & Time
x/24 – x equals number of hours to be added or subtracted to the SYSDATE (e.g. 1/24 or -1/24)
date_format – Date format (e.g. MM/DD/YYYY or DD/MM/YYYY)
time_format – Time format (e.g. fmHH:MI:SS AM)
optional_string – Optional Timezone indicator (e.g. CST or MST)
Example: To_Char(sysdate – 1/24, ‘MM/DD/YYYY fmHH:MI:SS AM ” CST”‘)
Result: 04/15/2019 6:42:21 AM CST
Date comparison solution for different set preference dates & Also for date comparison with static and dynamic data.
CASE WHEN (TO_CHAR(TO_DATE({startdate}), ‘YYYY-MM-DD’)) >= (TO_CHAR(TO_DATE(‘2022-04-30′,’YYYY-MM-DD’), ‘YYYY-MM-DD’)) THEN 1 ELSE 0 END
// Greater than or equal to date comparison with static and dynamic data.
TO_CHAR({trandate},’Day, Mon. DD, YYYY’)
TO_DATE(SYSDATE, ‘DD.MM.YYYY’)
TO_CHAR(SYSDATE, ‘mm/dd/yyyy’)
TO_CHAR({trandate},’MM/DD/YY’)
TO_CHAR(sysdate,’mm/dd/yyyy’)
Sysdate date conversion to a specific time zone
to_char(sysdate+19/24,’DD-MON-YYYY HH24:MI:SS’)
19 is the hour difference between Sydney Time and Pacific Time, so this requires a manual change to account for a different time zone.