SQL Server 2008 R2: Errrors when sending ISO 8601 timestamp values as parameters to a parametrized insert statement
-
I have problem inserting datetime values into my SQL Server 2008 R2. The server is capable of handling ISO 8601 format, but If I send them as parameters, the SQL Server/OLE DB provicer, seems to be less tolerant on the timestamp string format. I use OLE DB from C++ and would like to make my insert statement parametrized
INSERT INTO BATCH VALUES (?, ?, ?, ?....
When I specify timestamp values on the ISO8600 format, "2010-10-23T19:41:56.002+02:00", I get errors. When enter the same timestamp format directly in the statement, I don't get errors
INSERT INTO BATCH VALUES ('2010-10-23T19:41:56.002+02:00', ?, ?, ?....
I have tried two different OLE DB providers. "Microsoft SQL Server Native Client 10.0" and "Microsoft OLE DB Provider for SQL Server" They both behave slightly differently. When sending the timestamp values as part of the SQL INSET, both providers accepts the following formats
2010-10-23 19:41:58.000
2010-10-23T19:41:57.001
2010-10-23 19:41:56.002+02:00
2010-10-23T19:41:55.003+02:00When sending timestamp as parameter:
2010-10-21 13:44:59.092 Is accepted by both
2010-10-21T13:44:57.092 Is rejected by Microsoft SQL Server Native Client 10.0 and accepted by the other
2010-10-23 19:41:56.002+02:00 Rejected by both providers
2010-10-23T19:41:55.003+02:00 Rejected by both providersThe both providers also report different errors Microsoft OLE DB Provider for SQL Server reports "Conversion failed when converting date and/or time from character string" which I may understand. The "Microsoft SQL Server Native Client 10.0" reports HRESULT=DB_S_ERRORSOCCURRED. I would be glad if someone could explain these differences, especially why I can send the timestamp stirng litterally in the sql but not as a parameter.
-
I have problem inserting datetime values into my SQL Server 2008 R2. The server is capable of handling ISO 8601 format, but If I send them as parameters, the SQL Server/OLE DB provicer, seems to be less tolerant on the timestamp string format. I use OLE DB from C++ and would like to make my insert statement parametrized
INSERT INTO BATCH VALUES (?, ?, ?, ?....
When I specify timestamp values on the ISO8600 format, "2010-10-23T19:41:56.002+02:00", I get errors. When enter the same timestamp format directly in the statement, I don't get errors
INSERT INTO BATCH VALUES ('2010-10-23T19:41:56.002+02:00', ?, ?, ?....
I have tried two different OLE DB providers. "Microsoft SQL Server Native Client 10.0" and "Microsoft OLE DB Provider for SQL Server" They both behave slightly differently. When sending the timestamp values as part of the SQL INSET, both providers accepts the following formats
2010-10-23 19:41:58.000
2010-10-23T19:41:57.001
2010-10-23 19:41:56.002+02:00
2010-10-23T19:41:55.003+02:00When sending timestamp as parameter:
2010-10-21 13:44:59.092 Is accepted by both
2010-10-21T13:44:57.092 Is rejected by Microsoft SQL Server Native Client 10.0 and accepted by the other
2010-10-23 19:41:56.002+02:00 Rejected by both providers
2010-10-23T19:41:55.003+02:00 Rejected by both providersThe both providers also report different errors Microsoft OLE DB Provider for SQL Server reports "Conversion failed when converting date and/or time from character string" which I may understand. The "Microsoft SQL Server Native Client 10.0" reports HRESULT=DB_S_ERRORSOCCURRED. I would be glad if someone could explain these differences, especially why I can send the timestamp stirng litterally in the sql but not as a parameter.
Try a parameterized query. I do not know how it works with C++, it should be something like:
OleDbCommand cmd = new OleDbCommand("Insert .....");
cmd.Parameters.AddwithValue("ParameterName1", timeStampValue);Note: you use here the timestamp value as such, you *must not* convert it to a string!