Question Re: How to insert Time Stamp for SQL Database in Technology Q&A

How to insert Time Stamp for SQL Database

Former Member — Mon, 30 Jun 2008 13:08:37 GMT

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

Re: How to insert Time Stamp for SQL Database

GabrielSagaya — Mon, 30 Jun 2008 13:31:01 GMT

This blog will help you

/people/alessandro.berta/blog/2005/10/04/datetime-datatypes-and-oracle-database

Re: How to insert Time Stamp for SQL Database

agasthuri_doss — Mon, 30 Jun 2008 13:35:55 GMT

Hi,

>The database is taking date but when i'm trying to insert along with the time,

See that in the Database you have declared the Variable to accomodate the Data & Time Stamp.

Regards

Agasthrui Dosss

Re: How to insert Time Stamp for SQL Database

Former Member — Tue, 01 Jul 2008 05:01:37 GMT

Hi Manisha,

Please go through the below link. It will certainly help you.

Thanks.

Re: How to insert Time Stamp for SQL Database

Former Member — Tue, 01 Jul 2008 05:25:33 GMT

Hi,

Use DateTrans function to translate the source date to target date in the required format. also check in the SQL that column is in DateTime data type.

Re: How to insert Time Stamp for SQL Database

Former Member — Tue, 01 Jul 2008 05:34:38 GMT

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:

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

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

<DateField hasQuot="No">CONVERT(DATETIME, '2005-01-01 01:23:45', 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

Re: How to insert Time Stamp for SQL Database

Former Member — Tue, 01 Jul 2008 05:53:13 GMT

Hi ALL,

The blog speaks about inserting into an oracle database. Does the same holds true for an SQL database also?

Please help!!

Re: How to insert Time Stamp for SQL Database

Former Member — Tue, 01 Jul 2008 06:07:37 GMT

Does SQL takes date time only in one specific format or we can give any format to insert date time into SQL from XI?

Re: How to insert Time Stamp for SQL Database

Former Member — Tue, 01 Jul 2008 06:11:17 GMT

Hi Manisha, Pushpinder,

Try using convert function istead of TODATE.

CONVERT ( data_type , expression , style )

Style ID Style Type

0 or 100 mon dd yyyy hh:miAM (or PM)

101 mm/dd/yy

102 yy.mm.dd

103 dd/mm/yy

104 dd.mm.yy

105 dd-mm-yy

106 dd mon yy

107 Mon dd, yy

108 hh:mm:ss

9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)

110 mm-dd-yy

111 yy/mm/dd

112 yymmdd

13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)

114 hh:mi:ss:mmm(24h)

20 or 120 yyyy-mm-dd hh:mi:ss(24h)

21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)

126 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)

130 dd mon yyyy hh:mi:ss:mmmAM

131 dd/mm/yy hh:mi:ss:mmmAM

the first 3 digits are to be used as style in the syntax for the format following it.

or try using the following UDF, provided incoming date format is same as the style id mentioned.

if(Date!="")

{

return "CONVERT(varchar,"Date",126)";

}

else

return Date;

Re: How to insert Time Stamp for SQL Database

Former Member — Tue, 01 Jul 2008 06:22:48 GMT

Hi All,

I have tried using the convert function. The statement we are trying to give to the SQL database is CONVERT('2008-15-01 08:19:36.688','YYYY-MM-DD HH24:MI:SS.FF',121). its still showing the following error Incorrect syntax near '2008-19-01 08:19:36.688'.

Regards

Manisha

Re: How to insert Time Stamp for SQL Database

Former Member — Tue, 01 Jul 2008 06:26:40 GMT

Hi Manisha,

Your statement is not correct.

CONVERT('2008-15-01 08:19:36.688','YYYY-MM-DD HH24:MI:SS.FF',121).

change to :-

CONVERT(datetime,'2008-15-01 08:19:36.688',121).

try now.

the style id 121 itself represents the format you want to send data.

Re: How to insert Time Stamp for SQL Database

Former Member — Tue, 01 Jul 2008 06:43:35 GMT

Hi,

I have given CONVERT(datetime,'2008-15-01 08:19:36.688',121) and the datatype in XI as well as database is DATETIME, but it is giving an error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Regards,

Manisha Gogineni

Re: How to insert Time Stamp for SQL Database

Former Member — Tue, 01 Jul 2008 07:01:46 GMT

Hi Manisha,

Try giving the below. I donot have an SQL server DB to test it at my end.

CONVERT(varchar,'2008-15-01 08:19:36.688',121) .

hope it helps.