sql server 2008: bcp_sendrow, issue: invalid time format

Question

Hi!

I'm having a problem "Invalid time format" when I try to use bcp functions(bcp_sendrow/bind etc.) to insert into a table having a datetime column. so as per googled examples, I populated a DBDATETIME structure, for example: if I wanted to populate 3rd august 2009, exactly 8am localtime, I'd populate the structure members like so:

dtdays=40026(num days since 19000101) and dttime = 28800000 (num millisecs since midnight)

(Also having bound the var. as a SQLDATETIME) if dttime is 7 digits wide(or less), then bcp suceeds(but obviously with a wrong time value (date part is ok) )

how can I sort this out? I've tried datetime2 /time etc. but nothing helped. please help , all advice/ideas most appreciated. (I'm using Sql server 2008, and (ODBC) sql native client 10.0)

thanks!

Answer 1

never mind.. got it finally (sort of) -DBDATETIME::dttime (num of millisecs since start of day) needs to be DIVIDED by 3.333333.. that way you get to the closest value to represent your time. (the field width is only 7 digits wide)..this probably has something to do with datetime type being able to represent only 1/3rd of a sec. accurately.... anyways, I tried using datetime2 with ODBC bcp api , but couldn't make the time part work please let me know if anyone finds a better way thanks!