How do inequality operators work with date values in SuiteQL queries? In particular, why do the following queries produce identical results when I know that 28 new transaction lines were created on 09/02/2025? 1. SELECT COUNT(*) FROM transactionline WHERE linecreateddate > '01/01/2025' AND linecreateddate <= '09/02/2025' 2. SELECT COUNT(*) FROM transactionline WHERE linecreateddate > '01/01/2025' AND linecreateddate < '09/02/2025'

AI generated answer (using internal Slack threads):

In SuiteQL, the inequality operators work with date values in a specific way. The date values are treated as strings and compared lexicographically. This means that '01/01/2025' is considered greater than '12/31/2024' because '01' is greater than '12' when compared as strings. The reason why your two queries are producing identical results is likely due to the way the date values are being interpreted. In the first query, you are asking for all transaction lines where the

linecreateddate

is greater than '01/01/2025' and less than or equal to '09/02/2025'. In the second query, you are asking for all transaction lines where the

linecreateddate

is greater than '01/01/2025' and less than '09/02/2025'. If the

linecreateddate

for the 28 new transaction lines is exactly '09/02/2025', then these lines would be included in the results of the first query but not the second. However, if the

linecreateddate

for these lines is any time on '09/02/2025' (i.e., the date includes a time component), then these lines would be included in the results of both queries. This is because '09/02/2025 000000' is considered less than '09/02/2025 010000' when compared as strings. To avoid this issue, you should use the

TO_DATE()

function to convert the string values to date values before comparing them. For example, you could modify your queries to look like this: 1.

SELECT COUNT(*) FROM transactionline WHERE TO_DATE(linecreateddate, 'MM/DD/YYYY') > TO_DATE('01/01/2025', 'MM/DD/YYYY') AND TO_DATE(linecreateddate, 'MM/DD/YYYY') <= TO_DATE('09/02/2025', 'MM/DD/YYYY')

2.

SELECT COUNT(*) FROM transactionline WHERE TO_DATE(linecreateddate, 'MM/DD/YYYY') > TO_DATE('01/01/2025', 'MM/DD/YYYY') AND TO_DATE(linecreateddate, 'MM/DD/YYYY') < TO_DATE('09/02/2025', 'MM/DD/YYYY')

****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******