Date formats supported in the Data type Transforms

[shiveeta.mattoo]

Hi,

While reading DATE/TIME/TIMESTAMP data from file, we encounter these data types in multiple formats.

However Teiid seems to accept only fixed formats for Date, Time and TimeStamp ( i.e. yyyy-mm-dd, HH:mm:ss, yyyy-mm-dd HH:mm:ss respectively).

I notice these patterns hardcoded in StringToDateTransform, StringToTimeTransform, and StringToTimestampTransform (teiid-common-core).

Can these patterns be made configurable?

Regards,

Shiveeta

[shawkins]

> 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.

[rareddy]

See here Date_Time Functions - Teiid 8.9 (draft) - Project Documentation Editor

[shiveeta.mattoo]

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.

[rareddy]

Can you also post one single line of your file that you are trying to parse?

[shawkins]

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.