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-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 providers
The 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 am using Visual Studio 2010 Professional.
I have created a new SQL Server 2008 Database Project, locally applied and deployed a simple scheme and stored procedure. Everything looks as it should until I try to create the unit test by right clicking the stored procedure, is this functionality only supported in Premium and Ultimate?
CREATE FUNCTION [dbo].[Split]
RETURNS @t TABLE
id int identity(1,1),
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
r.value('.','varchar(15)') as item
from @xml.nodes('//root/r') as records(r)
Then cross apply to return your desired column. eg.
declare @tmp table (value varchar(100))
insert into @tmp values ('ASD654-100-CCCEEE4444-200-')
insert into @tmp values ('QWEQWEQ-2000-JJJLLL-20-PPPBBMMNN-600-')
cross apply dbo.split(value,'-')
where id = 3
Last Visit: 31-Dec-99 19:00 Last Update: 8-Feb-23 17:24