Click here to Skip to main content
15,507,195 members
Home / Discussions / Database
   

Database

 
AnswerRe: Simple question from new sql server user ... Pin
PIEBALDconsult25-Oct-10 4:34
professionalPIEBALDconsult25-Oct-10 4:34 
GeneralRe: Simple question from new sql server user ... Pin
NeverHeardOfMe25-Oct-10 5:18
NeverHeardOfMe25-Oct-10 5:18 
GeneralRe: Simple question from new sql server user ... Pin
PIEBALDconsult25-Oct-10 10:22
professionalPIEBALDconsult25-Oct-10 10:22 
GeneralRe: Simple question from new sql server user ... Pin
Mycroft Holmes26-Oct-10 2:42
professionalMycroft Holmes26-Oct-10 2:42 
GeneralRe: Simple question from new sql server user ... Pin
NeverHeardOfMe26-Oct-10 3:00
NeverHeardOfMe26-Oct-10 3:00 
GeneralRe: Simple question from new sql server user ... Pin
Mycroft Holmes26-Oct-10 3:57
professionalMycroft Holmes26-Oct-10 3:57 
GeneralRe: Simple question from new sql server user ... Pin
PIEBALDconsult26-Oct-10 16:12
professionalPIEBALDconsult26-Oct-10 16:12 
QuestionSQL Server 2008 R2: Errrors when sending ISO 8601 timestamp values as parameters to a parametrized insert statement Pin
Per Nilsson25-Oct-10 0:24
Per Nilsson25-Oct-10 0:24 
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:00


When 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 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.
AnswerRe: SQL Server 2008 R2: Errrors when sending ISO 8601 timestamp values as parameters to a parametrized insert statement Pin
Bernhard Hiller25-Oct-10 0:32
Bernhard Hiller25-Oct-10 0:32 
QuestionDatabase Unit Testing with Visual Studio 2010 Pin
dotman124-Oct-10 17:38
dotman124-Oct-10 17:38 
QuestionModifiable field in sql server 2005 Pin
Zeyad Jalil24-Oct-10 2:55
professionalZeyad Jalil24-Oct-10 2:55 
AnswerRe: Modifiable field in sql server 2005 Pin
Mycroft Holmes24-Oct-10 12:57
professionalMycroft Holmes24-Oct-10 12:57 
GeneralRe: Modifiable field in sql server 2005 Pin
Zeyad Jalil24-Oct-10 22:24
professionalZeyad Jalil24-Oct-10 22:24 
GeneralRe: Modifiable field in sql server 2005 Pin
Mycroft Holmes24-Oct-10 23:00
professionalMycroft Holmes24-Oct-10 23:00 
GeneralRe: Modifiable field in sql server 2005 Pin
Zeyad Jalil25-Oct-10 0:52
professionalZeyad Jalil25-Oct-10 0:52 
GeneralRe: Modifiable field in sql server 2005 Pin
Mycroft Holmes25-Oct-10 1:49
professionalMycroft Holmes25-Oct-10 1:49 
QuestionDetach the Database Pin
future383923-Oct-10 16:35
future383923-Oct-10 16:35 
AnswerRe: Detach the Database Pin
future383923-Oct-10 17:08
future383923-Oct-10 17:08 
Questionretrieving column's data Pin
siang_wu_id23-Oct-10 1:00
siang_wu_id23-Oct-10 1:00 
AnswerRe: retrieving column's data Pin
Blue_Boy23-Oct-10 1:26
Blue_Boy23-Oct-10 1:26 
GeneralRe: retrieving column's data Pin
siang_wu_id23-Oct-10 1:37
siang_wu_id23-Oct-10 1:37 
GeneralRe: retrieving column's data Pin
Blue_Boy23-Oct-10 1:39
Blue_Boy23-Oct-10 1:39 
GeneralRe: retrieving column's data Pin
siang_wu_id23-Oct-10 1:44
siang_wu_id23-Oct-10 1:44 
AnswerRe: retrieving column's data Pin
thatraja23-Oct-10 1:27
professionalthatraja23-Oct-10 1:27 
GeneralRe: retrieving column's data Pin
siang_wu_id23-Oct-10 1:35
siang_wu_id23-Oct-10 1:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.