Oracle translator seems to support translation of patterns from SimpleDateFormat to oracle's patterns [1], but not for all of them [2]. If Teiid is not able to translate format then it retrieves date/time/timestamp field and format it [2]. However, if pattern ends with non-pattern/non-letter character, pattern is translated only partially and passed to Oracle DB [3] - I have tried number, *, /, \, etc. Similar behavior with postgresql translator [1] Query: select formattimestamp(timestampvalue, 'y') from bqt1.smalla where intkey=1; Source-specific command: SELECT TO_CHAR(g_0."TIMESTAMPVALUE", 'YYYY') FROM "DV"."SMALLA" g_0 WHERE trunc(g_0."INTKEY") = 1 [2] Query: select formattimestamp(timestampvalue, 'D') from bqt1.smalla where intkey=1; Source-specific command: SELECT g_0."TIMESTAMPVALUE" FROM "DV"."SMALLA" g_0 WHERE trunc(g_0."INTKEY") = 1 [3] Query: select formattimestamp(timestampvalue, 'G-y-M-w-W-D-d-F-E-a-H-k-K-h-m-s') from bqt1.smalla where intkey=1; Source-specific command: SELECT g_0."TIMESTAMPVALUE" FROM "DV"."SMALLA" g_0 WHERE trunc(g_0."INTKEY") = 1 Query: select formattimestamp(timestampvalue, 'G-y-M-w-W-D-d-F-E-a-H-k-K-h-m-s-') from bqt1.smalla where intkey=1; Source-specific command: SELECT TO_CHAR(g_0."TIMESTAMPVALUE", 'AD-YYYY------Dy-AM------') FROM "DV"."SMALLA" g_0 WHERE trunc(g_0."INTKEY") = 1 Document URL: Section Number and Name: Describe the issue: Suggestions for improvement: Additional information:
Steven Hawkins <shawkins> updated the status of jira TEIID-3547 to Resolved
Steven Hawkins <shawkins> updated the status of jira TEIID-3547 to Closed