How to save milliseconds to sql datetime?

Fred Jones

unread,

Dec 8, 2003, 2:22:12 PM12/8/03

to

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.

The Margolins

unread,

Dec 8, 2003, 10:06:45 PM12/8/03

to

Hi Fred,

Look at the datetime definition in BOL. It is internally stored as a decimal
allowing down to 1/3 of millisecond to be stored. What you are asking for is
a mere representation of the value. T-SQL CONVERT function shows
milliseconds and in those formats milliseconds can be passed to datetime
column.

Ilya

"Fred Jones" <fred...@hotmail.com> wrote in message
news:036f01c3bdc0$94bbc510$a101...@phx.gbl...

Ross McKay

unread,

Dec 8, 2003, 11:33:14 PM12/8/03

to

It would be nice if you could just assign the data in its original type
(most likely Variant/Date) to the destination field, but there is a
problem in the conversion of dates from datetime in ADO into Variant and
vice versa:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q297463&

So, you'll have to use a string. To preserve the milliseconds, append
them to the time portion of your string in the same way that SQL Server
presents them - .nnn

Now the problem is, I don't see a way to extract the milliseconds from a
Date field in VBScript.

Your best bets are:

* forget using VBScript to transform this field, just use a DataPump
transform

* convert the data to a string type in your source database, and
possibly also your target database, and handle the conversion back to
datetime in SQL Server

If you are pulling the data from a system that doesn't allow you to
convert the field to a string, then you are possibly screwed. Either
that, or you need an interim step that operates outside DTS.

Oh, actually, there might be a way to get the milliseconds from the
source field (I'm not untyping what I just typed though!):

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q327080&

That code is VB, not VBScript, so might not help. Also, I'm still not
sure whether you will get away with it at the target database side; you
might need to pump the data into a table with the field in question
defined as a char field, as I think the Variant to datetime conversions
might happen within ADO.

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"The lawn could stand another mowing; funny, I don't even care"
- Elvis Costello

Billy Yao [MSFT]

unread,

Dec 9, 2003, 1:22:33 AM12/9/03

to

Hi Fred,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this issue.

I think that Ross has pointed out a lot on your issue and I just want to add values here:

I don't think you can get milliseconds in VBScript, as Conversion from a Variant to Date type in
VBScript always causes precision lost.

One workaround is that you can send the value back as a string instead of datetime data type,
or you can just use a Copy Column Transformation for that one'milliseconds column'.

For more information, you can also reference the following article:

193869 HOWTO: Get Fractions of a Second from ADO adDBTimeStamp Field
http://support.microsoft.com/?id=193869

If there is anything more I can do to assist you, please feel free to post it in the group

Best regards,

Billy Yao
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.