Problem
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:
")" ...
"(" ...
"CHARACTER" ...
"MULTISET" ...
Cause
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")....
Solution
To work around this limitation, transform the source value from MMddyyyy
to yyyy-MM-dd
format before applying the SQL query. Here is one approach that can be used to do so.
- Sheet1 ColumnA - initial value
09302019
. - Transform it into
2019-09-30
using the formulaRIGHT(#A;4)+"-"+LEFT(#A;2)+"-"+RIGHT(LEFT(#A;4);2)
. - Create a SQL Sheet and introduce the desired SQL query.
Example.
Comments
0 comments
Please sign in to leave a comment.