[teiid-issues] [JBoss JIRA] (TEIID-5326) SAP IQ timestamp conversion to varchar wrong resulting format
Jan Stastny (JIRA)
issues at jboss.org
Mon Apr 23 06:56:00 EDT 2018
[ https://issues.jboss.org/browse/TEIID-5326?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jan Stastny updated TEIID-5326:
-------------------------------
Description:
Following Teiid query:
{code:sql}
SELECT TimeStampValue, LOWER(TimeStampValue) FROM BQT1.SmallA
{code}
is pushed as:
{code:sql}
SELECT g_0."timestampvalue" AS c_0, lcase(stuff(stuff(convert(varchar, g_0."timestampvalue", 102), 5, 1, '-'), 8, 1, '-')+convert(varchar, g_0."timestampvalue", 8)) AS c_1 FROM "bqt-server"."dvqe"."SmallA" AS g_0
{code}
and returns the string representation of timestamp as:
2000-01-0100:00:00
2000-01-0100:00:01
...
Notice the missing space between date and time part.
SAP IQ is capable of converting the value implicitly, e.g. when I issue query
{code:sql}
SELECT TimeStampValue, LOWER(TimeStampValue) FROM SmallA
{code}
directly against SAP IQ instance, I get following string format:
2000-01-01 00:00:00.000
2000-01-01 00:00:01.000
....
The same results are returned if I use an explicit convert like:
{code:sql}
SELECT TimeStampValue, LOWER(CONVERT(varchar,TimeStampValue,121)) FROM SmallA
{code}
For more formatting options see [SyBooks Online|http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/Convert.htm]
was:
Following Teiid query:
{code:sql}
SELECT TimeStampValue, LOWER(TimeStampValue) FROM BQT1.SmallA
{code}
is pushed as:
{code:sql}
SELECT g_0."timestampvalue" AS c_0, lcase(stuff(stuff(convert(varchar, g_0."timestampvalue", 102), 5, 1, '-'), 8, 1, '-')+convert(varchar, g_0."timestampvalue", 8)) AS c_1 FROM "bqt-server"."dvqe"."SmallA" AS g_0
{code}
and returns the string representation of timestamp as:
2000-01-0100:00:00
2000-01-0100:00:01
...
Notice the missing space between date and time part.
SAP IQ is capable of converting the value implicitly, e.g. when I issue query
{code:sql}
SELECT TimeStampValue, LOWER(TimeStampValue) FROM SmallA
{code}
directly against SAP IQ instance, I get following string format:
2000-01-01 00:00:00.000
2000-01-01 00:00:01.000
....
The same results are returned if I use an explicit convert like:
{code:sql}
SELECT TimeStampValue, LOWER(CONVERT(varchar,TimeStampValue,121)) FROM SmallA
{code}
For more formatting options see [SyBooks Online|http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38159.1600/doc/html/san1277395118633.html]
> SAP IQ timestamp conversion to varchar wrong resulting format
> -------------------------------------------------------------
>
> Key: TEIID-5326
> URL: https://issues.jboss.org/browse/TEIID-5326
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.12.13.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
>
> Following Teiid query:
> {code:sql}
> SELECT TimeStampValue, LOWER(TimeStampValue) FROM BQT1.SmallA
> {code}
> is pushed as:
> {code:sql}
> SELECT g_0."timestampvalue" AS c_0, lcase(stuff(stuff(convert(varchar, g_0."timestampvalue", 102), 5, 1, '-'), 8, 1, '-')+convert(varchar, g_0."timestampvalue", 8)) AS c_1 FROM "bqt-server"."dvqe"."SmallA" AS g_0
> {code}
> and returns the string representation of timestamp as:
> 2000-01-0100:00:00
> 2000-01-0100:00:01
> ...
> Notice the missing space between date and time part.
> SAP IQ is capable of converting the value implicitly, e.g. when I issue query
> {code:sql}
> SELECT TimeStampValue, LOWER(TimeStampValue) FROM SmallA
> {code}
> directly against SAP IQ instance, I get following string format:
> 2000-01-01 00:00:00.000
> 2000-01-01 00:00:01.000
> ....
> The same results are returned if I use an explicit convert like:
> {code:sql}
> SELECT TimeStampValue, LOWER(CONVERT(varchar,TimeStampValue,121)) FROM SmallA
> {code}
> For more formatting options see [SyBooks Online|http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1510/html/iqrefbb/Convert.htm]
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
More information about the teiid-issues
mailing list