I know this is the SuiteScript space but wondering...
# suitescriptj
JC
01/06/2025, 4:37 PMI know this is the SuiteScript space but wondering if anyone can validate if this is a valid query?
SELECT * FROM customer WHERE TO_CHAR(TO_DATE(datecreated, 'MM/dd/yyyy'),'yyyyMM') = 201811
we're having issues querying this from another platform that's authenticated to NetSuite...
b
battk
01/06/2025, 5:14 PMits valid in some accounts, but invalid in others
battk
01/06/2025, 5:14 PMyou are essentially converting a date to a char, then back to a date, then back to a char
battk
01/06/2025, 5:15 PMwith that first date to char being implicit
battk
01/06/2025, 5:16 PMTO_DATE works on chars, so your datecreated is converted to a char first, using the user's date format setting
battk
01/06/2025, 5:16 PMthose date format settings can be different between users
battk
01/06/2025, 5:16 PMskip the messiness and just convert the date to a char once
j
JC
01/06/2025, 6:24 PM@battk Thanks! I will suggest this to the consultant
m
Mark Oriend
01/07/2025, 2:27 PM@JC
Please use below.
Copy code
SELECT * FROM customer WHERE TO_CHAR(datecreated, 'yyyyMM') = '201811'💯 1
Mark Oriend
01/07/2025, 2:28 PMThere is no need to convert datecreated to date type, because it's already date type.
Mark Oriend
01/07/2025, 2:29 PMAnd also, the TO_CHAR function returns string type, so the comparison value must be string type, not integer type
j
JC
01/07/2025, 10:19 PM@Mark Oriend Thanks! I'll give this a try
2 Views