SELECT on TIMESTAMP field from ABAP with EXEC SQL

[Former Member]

Hello,

I'm trying to get a field of one table which is defined as TIMESTAMP. MaxDB parameter DATE_TIME_FORMAT is set to INTERNAL. When I do the SELECT in SQL Studio I get ISO format 'YYYY-MM-DD HH:MM:SS.MMMMMM' back. So I tried a SELECT with ISO in WHERE clause, but I'm always getting a shortdump with this error:

Database error text........: "POS(82) Invalid date input value"

Database error code........: "-3065"

Then I did a SELECT without a WHERE clause in ABAP and got value '06-FEB-09' back from this field. So I tried with this ABAP statement and got no shortdump, but I also need to add time and not only the date.

EXEC SQL.

SELECT recv_time INTO :l_time FROM ztest WHERE sent_recv_time = '06-FEB-09'

ENDEXEC.

I'm using Native SQL because the SELECT is on a table which is not located in SAP Schema User. "SELECT recv_time FROM ztest WHERE recv_time = '2009-02-24 10:02:55.888000'" works in SQL studio, but not from ABAP.

Does anyone know which format I need to specify in the WHERE clause?

Regards

Markus Karsch

Edited by: Markus Karsch on Feb 26, 2009 4:22 PM

[Former Member · Accepted solution]

Hi,

MaxDB knows two so-called SQLMODEs: INTERNAL and ORACLE.

They both differ in some cases. The ORACLE-mode had been introduced to be a little bit more Oracle-compliant than with the internal mode without throwing that away because of customers using it for years.

In Oracle-mode we know one datatype holding dates: DATE. This includes date and time, but only the date is output per default.

In Internal mode MaxDB knows three datatypes: DATE (just year, month, dayofmonth), TIME and TIMESTAMP (from year down to microseconds).

Therefore Oracle-DATE and Internal-TIMESTAMP are stored in the same way.

ABAP uses the Oracle-mode. Therefore TIMESTAMP/DATE-values use this format:

'DD-MON-YY', for example ''06-FEB-09'

If you need to specify the time as well, you can use the function TO_DATE

TO_DATE('2000-05-10 17:20:07','YYYY-MM-DD HH24:MI:SS')

or

TO_DATE('20000510172007', 'YYYYMMDDHH24MISS')

or

....

whatever you want.

Please refer to the reference manual for the full choice of formats.

Good luck,

Elke

[Former Member · Accepted solution]

>

> ABAP uses the Oracle-mode. Therefore TIMESTAMP/DATE-values use this format:
> 'DD-MON-YY', for example ''06-FEB-09'
> If you need to specify the time as well, you can use the function TO_DATE
> TO_DATE('2000-05-10 17:20:07','YYYY-MM-DD HH24:MI:SS')
> or
> TO_DATE('20000510172007', 'YYYYMMDDHH24MISS')
> or 
> ....
> whatever you want.
> Please refer to the reference manual for the full choice of formats.
> 
> Good luck,
>   Elke

Hi Elke,

that was the solution. Many thanks for your fast reply!

Regards

Markus

[TTK]

Hello Markus

I don't know whether this will work from ABAP, but at least MaxDB can internally handle the ODBC literals for time, date and timestamp.

Therefore literals like

"{d'0001-02-03'}"

"{t'01:02:03'}"

"{ts'0001-02-03 04:05:06'}"

might work. See [http://msdn.microsoft.com/de-de/library/ms190234(SQL.90).aspx]

HTH & regards Thomas

[Former Member]

>

> Hello Markus
> 
> I don't know whether this will work from ABAP, but at least MaxDB can internally handle the ODBC literals for time, date and timestamp.
> 
> Therefore literals like
> 
> "{d'0001-02-03'}"
> "{t'01:02:03'}"
> "{ts'0001-02-03 04:05:06'}"
> 
> might work. See [http://msdn.microsoft.com/de-de/library/ms190234(SQL.90).aspx]
> 
> 
> HTH & regards  Thomas

Hi Thomas,

Thanks for your help. Unfortunately doesn't seem to work, I get following shortdumps (tried with 3 different notations):

Database error text........: " "

Database error code........: "-4005"

Triggering SQL statement...: "SELECT xxxxxx, status, sent_xxxx_time FROM

xxx_xxxxxxxxx WHERE sent_recv_time = "{ts'2009-02-06 04:05:06'}""

Database error text........: "POS(87) Invalid keyword or missing delimiter"

Database error code........: "-3008"

Triggering SQL statement...: "SELECT xxxxxx, status, sent_xxxx_time FROM

xxx_xxxxxxxxx WHERE sent_recv_time = '{ts' 2009-02-06 04:05:06 '}'"

Database error text........: "POS(81) Missing value specification"

Database error code........: "-5010"

Triggering SQL statement...: "SELECT xxxxxx, status, sent_xxxx_time FROM

xxx_xxxxxxxxx WHERE sent_recv_time = { ts '2009-02-06 04:05:06.000' }"

Regards

Markus

[TTK]

Hm, I don't see a difference of your third try and my example:

create table tab (id int, timst timestamp)

insert into tab values (1, now())

select * from tab where timst < {ts '2010-01-01 18:22:13.0001'}

So, it might be, that the SAPR3-mode of MaxDB could be the reason. Don't know, whether this problem can be solved with the ODBC literals.

Regards Thomas