Netsuite SuiteQL query to get access token created before 2800 days ago
I am trying to get access tokens with below condisions,
- inactive is false,
- token creation date is before 2800 days using suiteQL.
I tried writiing this query but it doesn’t work. Getting error as I am getting error as ‘{“type”:”error.SuiteScriptError”,”name”:”SSS_SEARCH_ERROR_OCCURRED”,”message”:”Search error occurred: Invalid or unsupported search”,”id”:””,”stack”:[“Error\n at generateTokenSearchResultsTable function’ Error at query.runSuiteQL api. I think only number/string can be passed to parameter.
Please advice thank you!
// Calculate the date 2800 days ago
let today = new Date();
today.setDate(today.getDate() - 2800);
const pastDate = today.toISOString().split('T')[0]; // "YYYY-MM-DD"
let formattedDate = format.parse({value:pastDate, type: format.Type.DATE})
const sqlStr = `SELECT OAuthToken.tba_token_name AS tokenName,
OAuthToken.dCreated AS createdDate FROM OAuthToken
WHERE OAuthToken.bInactive = 'F'
AND CAST(OAuthToken.dCreated AS DATE) < ?`
const tokens = query.runSuiteQL({
query: sqlStr,
params: [formattedDate]
}).asMappedResults();
Finally, this query worked to get tokens created 2800 days ago:
const sqlStr = `SELECT *
FROM OAuthToken
WHERE OAuthToken.bInactive = ‘F’
AND OAuthToken.dCreated < CURRENT_DATE – 2800`;
const tokens = query.runSuiteQL({
query: sqlStr,
}).asMappedResults();
