On Sheet1 column A, I’ve created a record using the T function: T("09302019"). I then converted this string value into a date using ASDATE(#A;"MMddyyyy") function.
However, trying the query:
SELECT CAST(Sheet1.A as date 'mmddyyyy') from Sheet1
Throws the following exception:
WARN [2019-09-30 17:35:29.806] [qtp1942406066-77] (SqlSheetModel.java:199) - Something went wrong while parsing SQL query: SELECT CAST(Sheet1.A as date 'mmddyyyy') from Sheet1, Encountered "\'mmddyyyy\'" at line 1, column 30.
Was expecting one of:
Under the hood, SQL Worksheets are converted into a set of traditional Datameer functions and operations. The library it uses to cast a string into a date has a hardcoded pattern which is
yyyy-MM-dd. As a result, any other pattern will not be recognized. This is why Datameer throws an exception for a query like:
SELECT CAST(receipt_date as DATE; "MMddyyyy")....
To work around this limitation, transform the source value from
yyyy-MM-dd format before applying the SQL query. Here is one approach that can be used to do so.
- Sheet1 ColumnA - initial value
- Transform it into
2019-09-30using the formula
- Create a SQL Sheet and introduce the desired SQL query.