How to store string with milliseconds as datetime?

[fredjonze]

Hi,
I'm trying to use VBScript in DTS to convert a time string, with tenths of a second, to a sql 2k datetime value. The standard ISO format of "yyyy/mm/dd hh:mm:ss AM/PM" doesn't seem to support fractions of a second. Is there a way in DTS Active-X script to assign a string value like "2003-11-07 17:23:33.4" to a SQL datetime column?

Thanks for any advice.

 

[nigelrivett]

yyyymmdd hh:mm:ss.nnn

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[fredjonze]

Hi Nigel,

Thanks for the fast answer. However, the code:

DTSDestination("bmt_dt_sys_datetime&quot = "20031107 22:10:23.100"

creates an "invalid data value" error. That has been my problem.

Thanks for any hints.

 

[SQLBill]

The trouble you are having is that SQL Server really DOES NOT use milliseconds. It's the NEAREST three-hundreths of a sescon (3.33 milliseconds). So not just any millisecond will be acceptable, only multiples of three-hundreths of a millisecond are acceptable.

-SQLBill