How to insert Time Stamp for SQL Database

[Former Member]

Hi All,

I need to insert timestamp in milliseconds into the SQL database. The database is taking date but when i'm trying to insert along with the time, it is throwing the following error "Incorrect syntax near 12".

I have given the datatype as DateTime in XI.

Please let me know how i can solve this issue.

Regards,

Manisha Gogineni

[Former Member]

Dear Manisha,

See this Note from SAP:

Date Field Handling

o Q: I would like to insert a date field into a table using the JDBCReceiver Adapter. However, I always get an error about a data type conversion error. What is the procedure to insert a date field using a JDBC Receiver?

o A: The actual implementation depends on the DBMS you are using.This answer will first describe a generic approach to implement this functionality and then provide examples for popular DBMS. All DBMS offer some kind of functionality to convert one data type into another data type. As the XML document used by the JDBC Receiver is based on character strings, we need to use a conversion function to convert a string into a DBMS-specific date type. This conversion function will be embedded by the JDBC Receiver into the SQL statement sent to the DBMS. In order that the DBMS actually

executes the conversion function and does not treat it as a string, we need to make sure that the JDBC Adapter does not quote the date parameter. This can be achieved by setting the hasQuot attribute of the respective date field's XML element to "No".

An example for the Oracle DBMS, where the date conversion function is named TO_DATE:

<DateField hasQuot="No">TO_DATE(&apos;2004-07-20 08:00:00&apos;, &apos;yyyy-mm-dd hh:mi:ss&apos;)</DateField>

As you can see, any occurrence of an apostrophe within the element data needs to be written as "&apos;" in order to yield valid XML.

For the Microsoft SQL Server DBMS, the statement looks as follows:

<DateField hasQuot="No">CONVERT(DATETIME, &apos;2005-01-01 01:23:45&apos;, 120)</DateField>

The third parameter specifies the date format expected by the

CONVERT function. For details, refer to the MS SQL Server

documentation (Books Online -> Contents -> Transact-SQL Reference

-> CAST and CONVERT).

Hope with this you can solve time stamp as well .

Best Regards

Praveen K