https://netsuiteprofessionals.com logo
Join Slack
Powered by
gaah, I'm going crazy. I'm trying to convert a dat...
# suiteanalytics
w
gaah, I'm going crazy. I'm trying to convert a date that is stored as text into a formula(date). All rows have valid dates in the format YYYY-MM-DD. But when I use to_date({field},'YYYY-MM-DD') I get invalid expression. Could it be because I have +700k records in that table?
I was trying to use the formula in a criteria and compare it in a script with a script parameter of the type Date.But I had to resort to a numeric formula instead with converting both to a numerical value of YYYYMMDD.
✅ 1
z
I believe there may be various issues at play here: (1) SQL functions for converting between dates and strings; (2) interpreting strings as dates in SuiteScript; (3) setting date values on records in SuiteScript. (1) If your search result set will be consumed by a script, you may as well leave the date string as a character type. Do the conversion in the script using N/format. If you do require a date formula in the search itself, review the Oracle SQL documentation for TO_DATE to make sure that your usage checks out. The number of records in the table is not/never the problem. (2) In scripts, in general, use N/format to manage conversion between date-as-string and date-as-JavaScript object. The purpose of that module, as far as dates are concerned, is to reduce the complexity that arises with localization and similar concerns involving the representation of date and datetime values in strings. (3) SS2.0 APIs in general treat date values as JavaScript Date objects. For example, if you retrieve the value of a Date field via record.Record.getValue(), the return type is Date (not String). Likewise, if you set a field value, and the field type is Date, be sure to use a Date object (not a string) for the value in record.Record.setValue(). It may be that you produce that Date object using N/format (that is, converting a string).
Note: if you're in the middle of developing a script and want to check the prototype of an object (in this case, to make sure it's of the Date prototype), this is a boilerplate formulation that works in pre-SS2.1 JavaScript: 
Object.prototype.toString.call(myVariable)
w
I wanted to use to_date() in a scripted search criteria. I wanted this because I don't want to loop through a bunch of results that I don't need to process. The formula to_date() is pretty stright forward and it works in the UI when using it in a non-stored formula field. However, that same field doesn't work in a search. All values are of the length 10, have the count of 2 for the char "-" and all follow the same pattern. REGEXP_INSTR({field}, '([12]\d{3}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01]))') returns 1 for all values. I got it to work using this formula. The search returns all rows of the table and no row shows '1970-01-01': CASE WHEN REGEXP_INSTR({field}, '([12]\d{3}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01]))') = 1 THEN TO_DATE(substr({field},1,10),'YYYY-MM-DD') ELSE to_date('1970-01-01','YYYY-MM-DD') END I could even do any of below: CASE WHEN REGEXP_INSTR({field}, '\d') > 0 THEN TO_DATE({field},'YYYY-MM-DD') END DECODE(REGEXP_INSTR({field}, '\d'), 0, null, TO_DATE({field},'YYYY-MM-DD')) DECODE(REGEXP_INSTR({field}, '\d'), 1, TO_DATE({field},'YYYY-MM-DD')) Below formula doesn't work: CASE WHEN {field} IS NOT NULL THEN TO_DATE(substr({field},1,10),'YYYY-MM-DD') ELSE to_date('2020-01-01','YYYY-MM-DD') END
I've tried creating a test record with only 10 entries and the plain formula still gives ERROR: Invalid Expression. I'm creating a case.
I've tested with other string-formats and the issue arise when the year is in the beginning in the mask:
z
My bad - this is in #C2A1ZEMF0 not #C29HQS63G My reply must have seem very crazy. In the context of search, I too have had trouble with the implementation of 
TO_DATE()
although I cannot remember what exactly. If I could please ask one dumb question: is the underlying field a DATE or a DATETIME? And if it is a DATETIME, is it a custom field or a built-in timestamp field such as 
created
or 
lastmodified
?
Note on the regular expression: it is good, as you know. For exact matches or to test the assumption that the field content is being evaluated as a pure date expressed in a particular localization, I would suggest anchoring the expression: 
^(19|20)\d{2}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$
The outer parentheses are unnecessary, by the way. I updated the part of the expression that models the year, too, to be more exacting, along the lines of your very nice modeling of month and day.
The thing about the regex that is not so great, as I'm sure you know, is that the evaluation of a date as string is coupled to some kind of localization. I'm not sure, in the context of saved search, whether the localization would be that of the Company Preference or that of the User Preference. (Depending on the configuration, the user may not have the ability to override company-level config.) In any case, I'm absolutely of the same mind in being disappointed by regex qua solution: it should be possible to use 
TO_DATE
, which is expressly for these kinds of things that need to be localization-aware.
Toward my initial question above, regarding the data type: if it turns out that the data type is not a true date, then it may be necessary to cast it to a date first. I cannot remember what kind of cast/convert options are available in the SQL subset implemented in NetSuite search. Please let me know if you want me to experiment with it on my end. I do believe that filing a case was absolutely the right thing to do. Even if there is a way toward a solution, the documentation is unclear here, and the expectations surrounding 
TO_DATE
are not being met adequately.
w
@zvictor Hi, the field is a Free-Form Text-field containing a string in the format YYYY-MM-DD. I believe all your posts have missed that fact.
Copy code
I'm trying to convert a date that is stored as text into a formula(date). All rows have valid dates in the format YYYY-MM-DD
z
Ah, thank you.
5 Views
Open in Slack
PreviousNext
Powered by