-
1. Re: Date formats supported in the Data type Transforms
shawkins Oct 10, 2014 10:24 AM (in response to shiveeta.mattoo)1 of 1 people found this helpful> Can these patterns be made configurable?
No, this was done to match the default expectations of SQL, the java.sql code, and ensure a canonical form for pushdown support. There are parse/format functions that can be used though if more conversions are needed.
-
2. Re: Date formats supported in the Data type Transforms
rareddy Oct 10, 2014 10:45 AM (in response to shawkins)1 of 1 people found this helpful -
3. Re: Date formats supported in the Data type Transforms
shiveeta.mattoo Jan 12, 2015 12:22 PM (in response to rareddy)Thank you Ramesh and Steve.Here is the SQL query I am trying with -
SELECT A.id,A.date1 FROM (EXEC fixedwidthdate.getFiles('FixedWidthDate.txt')) AS F, TEXTTABLE(TO_CHARS(F.file,'UTF-8') COLUMNS id Integer width 2,parsedate(date1,'dd-mm-yyy') Date width 10 NO ROW DELIMITER) AS A
But I get this error on deploying the VDB -
org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "2,parsedate[*]([*]date1," at line 1, column 156.
Was expecting: "string" | "varbinary" | "varchar" | "boolean" | "byte" | "tinyint" | "short" | "smallint" | "char" | "integer" ...
I am trying to read a fixed width file which contains dates like 10-10-2014.
Can you point me to what could be wrong in the query.
-
4. Re: Date formats supported in the Data type Transforms
rareddy Jan 12, 2015 12:47 PM (in response to shiveeta.mattoo)Can you also post one single line of your file that you are trying to parse?
-
5. Re: Date formats supported in the Data type Transforms
shawkins Jan 12, 2015 1:14 PM (in response to rareddy)That texttable syntax is not correct. The only date format supported by default is the JDBC standard yyyy-MM-dd. If you need a different format, you would just parse the column as a string and then in the select clause put the appropriate parsedate/conversion function.