I have a sql returning 02/05/2024 8:40 am. When I format that and then try and set my DateTime field like the following it sets the time to 11:40 am.

var nyTime = format.format({
        value:date,
        type:format.Type.DATETIME,
        timezone:format.Timezone.AMERICA_NEWYORK
    });

record.submitFields({
                        type: 'customrecord_dc_wo',
                        id: wo,
                        values: {
                            custrecord_dc_wo_eq_lot_check_created: new Date(nyTime)
                        }

})

What type of script is this?

MapReduce

are you in another time zone? because that can affect how it comes out

Im EST time zone

I believe the timezone of any user-less server-side script is Pacific. So my guess is that

nyTime

outputs 08:40 as a string, then when you pass that through

new Date()

it assumes Pacific

yes correct

Do you need

date

as a string at all? If you just leave it as a

Date

instance (i.e. pass

date

as the value for the field), does this go away?

if you just set recordSubmit with date instead of

new Date(date)

you get an error:

ERROR String: {"type":"error.SuiteScriptError","name":"INVALID_FLD_VALUE","message":"You have entered an Invalid Field Value 02/08/2024 08:40 am for the following field: custrecord_dc_wo_eq_lot_check_created"

I was expecting

date

to be a

Date

instance

I tried new Date(date) as well and it still sets 11:40

Yeah, your

timezone

s/b Pacific iso New York

So, just out of the SQL it outputs 8:40am then I do new Date(date). Are you suggesting getting pacific time from the sql first then using that in the Date instance? Because format utilizing pacific doesn't set right.

if you are just using format.format to change the timezone, you can do that using the Date object and then use it to set the field. I think that is what he is saying.

// Assuming the string:
date = "02/05/2024 8:40 am"

// I'm suggesting
record.submitFields({
    type: 'customrecord_dc_wo',
    id: wo,
    values: {
      custrecord_dc_wo_eq_lot_check_created: new Date(date)
   }
})

The

Date

constructor will assume Pacific, and the SQL should be returning Pacific, so I'm not sure there's a need to do any conversion

there are 2 timezones of importance here

So, here is the SQL output and the custom record created date. I was running just like your code there @erictgrubaugh and the field was setting as 11:40am instead

the second is the timezone of your netsuite user

yes, company and user is EST

you then do

format.format({
        value:date,
        type:format.Type.DATETIME,
        timezone:format.Timezone.AMERICA_NEWYORK
    });

Right, so doing the following sets the field at 11:40 am.

//DATE FROM SQL
var date = '02/05/2024 8:40 am';

var formattedDate = format.format({
    value: date,
    type: format.Type.DATETIME,
    timezone: format.Timezone.AMERICA_NEWYORK
});

record.submitFields({
    type: 'customrecord_dc_wo',
    id: wo,
    values: {
        custrecord_dc_wo_eq_lot_check_created: new Date(formattedDate)
    }
})

format.format can turn a date into a string

ahhh yes I see what you mean, thanks @battk format.parse worked perfect

So the query is returning Eastern time because the User in this context is the System user, and the System user uses the Company time zone, which is Eastern?

Yes, user (me) is set as eastern and under company information its Eastern

You're not the User in a M/R though, so your timezone doesn't come into play until display time in the UI. I was expecting the query to return Server time rather than Company time, so that's what I'm trying to clear up.

ohh sorry. yeah, the sql was returning eastern from what I can tell and not pacific/server time

a scheduled deployment of a map/reduce script will run using the system user (same for a script executed via the save and execute button)

company, user, and subsidiaries are all in Eastern, so was confused when it set as pacific. Thanks for your help

Thanks, that helps.

I had specifically had issues with this because I needed to run saved search date queries...

that note isnt specific enough, but its referring to the behavior of Date formatting, which is not the same as DateTime formatting

If you can have the sql return the iso timestamp shouldn't you be able to just run new Date() on it:

to_char(sys_extract_utc(systimestamp), 'yyyy-mm-dd"T"hh24:mi:ss"Z"')

@Watz, I've used this approach before with success. I wonder if a blog with some sort of event/script matrix would help here.