I currently have a search where I’m retrieving

message

records within the past 2 days, by doing a

WHERE message.messagedate > 'date_im_comparing_goes_here'

(the formatting for the date is obtained using moment() and format module, like so:

let two_days_ago = format.format({value: moment().subtract(2, 'days').toDate(), type: format.Type.DATE});

which gives me something like ‘14-Oct-2023’ to compare with in my SQL. I’d like to modify this to use a number of hours instead, e.g. the last 8 hours. I can use my format/moment call to get a date with time by using

let eight_hours_ago = format.format({value: moment().subtract(8, 'hours').toDate(), type: format.Type.DATETIME});

which gives me something like ‘16-Oct-2023 02816’ but when I try to use that in my

WHERE

I get

Invalid or unsupported search

. Anyone know what format I need to use to compare datetime in SuiteQL? For dates it seems to be whatever is used in format module….

You can probably use SYSDATE-(8/24) in your query instead

I’ll give that a whirl

Besides that solution. You can also convert the string into a sql date with to_date('2023-05-03 13:40', 'YYYY-MM-DD HH24:MI')

No worries. I left it as 8/24 just to make it obvious what it's doing

Example on formatting a date as UTC ISO8601 with SuiteQL:

SELECT to_char(sys_extract_utc(SYSTIMESTAMP), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS current_time

oh yeah I know how to TO_CHAR to format it in SELECT, was just struggling with my WHERE. It’s working now tho.