Which data type should I define my date value as w...

Former Member · ‎09-15-2014

Hi,

I have been struggling for a few weeks now with a problem in my ABAP program. I have an ABAP program where I need to supply a date which is then passed to MSSQL. It seems that neither "String" nor "Type c" is the correct definition of this data type. Could anyone please assist me as to what it should be?

Here is an extract of the full ABAP code:

----------------------------------------------------

FUNCTION Z_WB_EXT_TREK_00.

*"----------------------------------------------------------------------

*"*"Local Interface:

*" IMPORTING

*" REFERENCE(WBCODE) TYPE ZWB_TS_CODE

*" REFERENCE(DATE_LOW) TYPE DATS

*" REFERENCE(DATE_HIGH) TYPE DATS

*" EXPORTING

*" REFERENCE(WB02_EOF_RECS) TYPE ZWB_TS_INT_EOF_WB02_TAB

*"----------------------------------------------------------------------

tables: zwb_tS_DEF.

data: begin of iTab occurs 0.

include structure ZWB_TS_INT_EOF_WB02.

data: end of iTab.

data: SQLDbName TYPE dbcon-con_name.

data: DataString(512) type c.

data: RecCount type i.

data: date1_Low type ZWB_TS_DATETIME, " Parameter for selecting on SQL field DATE1 (lower bound)

date1_high type ZWB_TS_DATETIME. " Parameter for selecting on SQL field DATE1 (upper bound)

data: date1_format type ZWB_TS_DATETIME. " Format for selection date

data: date1_low_string type string, " SQL query argument for date selection (lower bound)

date1_high_string type string. " SQL query argument for date selection (upper bound)

data: SQLDelimiter(1) type c value ''''. " Character delimiter for strings passed to SQL server

*-----------------------------------------------------------------------

Select single * from zwb_ts_def where wbcode = wbcode.

check sy-subrc = 00.

SQLDBName = Zwb_ts_def-DBCON_NAME.

** Properly format the selection criteria (dates, formats)

date1_format = '121'.

concatenate date_low(4) '-' date_low+4(2) '-' date_low+6(2) ' 00:00:00.000' into date1_low.

concatenate date_high(4) '-' date_high+4(2) '-' date_high+6(2) ' 23:59:59.999' into date1_high.

concatenate 'convert(datetime,' sqldelimiter date1_low sqldelimiter ',121)'

into date1_low_string.

concatenate 'convert(datetime,' sqldelimiter date1_high sqldelimiter ',121)'

into date1_high_string.

concatenate '''' date1_low '''' into date1_low_string.

concatenate '''' date1_high '''' into date1_high_string.

** Open the DB connection

EXEC SQL.

CONNECT TO :SQLDBName

ENDEXEC.

check sy-subrc = 0.

** Set up an extract dataset, using dbcur as cursor placeholder

EXEC SQL.

OPEN dbcur FOR

SELECT

CARDID,

REGNO,

DATE1,

DATE2,

MASS1,

MASS2,

NETT,

CONSNO,

FIRST,

DIR,

STOCKPILE,

STOCKPILENO

FROM eof

WHERE

* regno = 'DECOR001'

* eof.DATE1 > '2014-06-05 00:00:00.000' and eof.date1 <= '2014-06-05 23:59:59.999'

eof.DATE1 > :DATE1_LOW_STRING and eof.DATE1 <= :DATE1_HIGH_STRING

ENDEXEC.

** Read the database one record at a time

refresh iTab. " Clear any existing entries from the internal table

do.

EXEC SQL.

fetch next dbcur into

:iTab-ZZCARDID,

:iTab-ZZREGNO,

:iTab-ZZDATE1,

:iTab-ZZDATE2,

:iTab-ZZMASS1,

:iTab-ZZMASS2,

:iTab-ZZNETT,

:iTab-ZZCONSNO,

:iTab-ZZFIRST,

:iTab-ZZDIR,

:iTab-zzSTOCKPILE,

:iTab-ZZSTOCKPILENO

ENDEXEC.

if sy-subrc <> 0. exit. endif.

append itab.

add 1 to RecCount.

enddo.

** Close the DB connection

EXEC SQL.

CLOSE dbcur

ENDEXEC.

EXEC SQL.

DISCONNECT :SQLDBName

ENDEXEC.

refresh WB02_EOF_RECS.

loop at itab.

append itab to WB02_EOF_RECS.

endloop.

ENDFUNCTION.

-----------------------------------------------------------------------------------------------------------------------

I have attached a screenshot of the error message I am getting when passing the following date: 05062014

Any help please, will be greatly appreciated.

Former Member · ‎09-15-2014

Hi Juandre,

Please Check the below link for some other declaration format for Date.

Regards,

Vasanthi

Former Member · ‎09-16-2014

Hi, I managed to get my data type defined accordingly and tested through execution of the program in SAP, it retrieved the data from SQL and displayed it correctly.

Now my next step where I got stuck, I have an HTML page in a BSP Application which calls this function module. Basically, you would enter a date on the HTML page which has a form for the purpose, then when you click SUBMIT it should send that information from the date field into the Function Module so that the SQL Select Statement which is already defined, can make use of the information supplied on the HTML page, to run the SELECT query.

Although I am getting a rather unhelpful error message which means nothing to me, could anyone tell me what it is?

An exception with the type CX_SY_NATIVE_SQL_ERROR occurred, but was neither handled locally, nor declared in a RAISING clause

-----------------------------

This is the "Layout" part of the HTML in the BSP Application. This section is the FORM container:

<form>

<div>

<h1 class="auto-style2">Date Range Query :</h1>

<label>

<span><span class="auto-style2">Start Date</span>  </span>

<input id="input_date1_low" type="text" name="name" /><span class="auto-style2">End

Date</span>  

</label>

<label>

</label>

</div>

</form>

------------------------------

This is the "OnInputProcessing" section in the Event Handler of the same HTML page in the BSP.

data: date1_low TYPE ZWB_TS_DATETIME,

date1_high TYPE ZWB_TS_DATETIME,

form_date_low TYPE c,

form_date_high TYPE c,

date_low TYPE dats,

date_high TYPE dats.

case event_id.

when 'submit'.

form_date_low = request->get_form_field('input_date1_low').

form_date_high = request->get_form_field('input_date1_high').

date_low = form_date_low.

date_high = form_date_high.

CALL FUNCTION 'Z_WB_EXT_TREK_00'

EXPORTING

date_low = w_date_low.

date_high = w_date_high.

Which data type should I define my date value as when passing it from ABAP to MSSQL 2008