JDBC Receiver DATETIME Error

[Former Member]

Hi,

I have a JDBC which gets the error below:

INSERT statement from JDBC LOG:

2010-03-09 10:36:32 Information

INSERT INTO TABLENAME (COLUMNS, COLUMNS, COLUMNS, COLUMNS, COLUMNS, COLUMNS, COLUMNS) VALUES (444444001, 2010, '20100104 00:00:00', 00444444, NULL, NULL, 0033333, 'XXXX')

*2010-03-09 10:36:32 Error Could not execute statement for table/stored proc. "TABLENAME" (structure "STATEMENT") due to com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting datetime from character string.

2010-03-09 10:36:32 Error Adapter Framework caught exception: null*

I tried all kind of formats. Like '2010-01-04', '2010.01.04', '2010-01-04 00:00:00', 2010-01-04 etc. but no result.

The field in MSSQL is DATETIME. My field in PI is string (also tried datetime).

Any idea?

[RameshGulipall · Accepted solution]

HI ,

you check the below weblog for data and time issue with DB.

http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=(J2EE3417500)ID0760453250DB00677869991075830602End...

hope it will use for issue.

Regards,

Ramesh

[Former Member]

You cannot use a string directly as datetime value for MS SQL Server. Use the CONVERT function, see here:

http://msdn.microsoft.com/en-us/library/aa226054%28SQL.80%29.aspx

CSY

[Former Member]

> I tried all kind of formats. Like '2010-01-04', '2010.01.04', '2010-01-04 00:00:00', 2010-01-04 etc. but no result.

Have you tried below format?

2010-01-04, 103632 (use comma to separate date and time stamp and in time stamp do not use colon) because when you are using curent date then your format looks like this one.. try this.

Regards,

Sarvesh

[Former Member]

Hi,

can you check once is the format of your database is different from the format what ur are specifying('2010-01-04', '2010.01.04', '2010-01-04 00:00:00', 2010-01-04).

Regards,

Rama

[former_member200962]

I tried all kind of formats. Like '2010-01-04', '2010.01.04', '2010-01-04 00:00:00', 2010-01-04 etc. but no result

What is the format that your DB is expecting? Did you try that particular format?

[MichalKrawczyk]

Hi,

>>The field in MSSQL is DATETIME. My field in PI is string (also tried datetime).

can you try changing the field in XML to xsd:date ?

and then: 2010-01-04

?

Regards,

Michal Krawczyk

[Former Member]

Hi,

Tried it, not working.

When I give in the mapping the currentDate function then it works???

This works:

INSERT INTO GIDEN_FATURALAR (COLUMNS, COLUMNS, COLUMNS, COLUMNS, COLUMNS, COLUMNS) VALUES (33333, 2010, 2010-03-09, 333333, NULL, NULL, 33333, 'BEKİR ERGENÇ')

When I try my date, which is in the same format (according to JDBC log) it gives an error?

[former_member200962]

When I give in the mapping the currentDate function then it works???

Is there any restriction imposed on the DB on the date to be accepted....also try passing the current date from the source node itself (remove the function) and check if the query is getting executed properly.