asked on

SQL Server 2000 DTS datetime

I wish to load the following data into a table using DTS.

162,Data Removed,Nov 21 2002 11:04:13:030AM

The table consists of three fields of type (int,varchar(50), datetime).

If I change the date time to 11:04:13AM DTS loads the record. If I leave the time as it is I get the following error message :-

ActiveX Scripting Transform AxScriptXform encountered an invalid data value for update destination column.

Why will DTS and vbscript not recognise the full time stamp.

All suggestions welcome

adathelad

I don't know why this error occurs, I've had the same problem. It's something to do with the fact that the date (Nov 21 2002 11:04:13:030AM) is loaded into the DTS package as a VARCHAR and then is impliclity being converted into a DATETIME.

This is equivalent to what is happening in your scenario and raises an error:
DECLARE @pstrDate VARCHAR
SET @pstrDate = 'Nov 21 2002 11:04:13:030AM'
INSERT MyTable (162,'Data Removed', @pstrDate)

However, this example does not generate the error:
(Note the date value has been declared a given size)
DECLARE @pstrDate VARCHAR(26)
SET @pstrDate = 'Nov 21 2002 11:04:13:030AM'
INSERT MyTable (162,'Data Removed', @pstrDate)

It is an annoying problem.

dorelb

If you don't specify a size for the varchar datatype, the default of 1 is used, so your string value is truncated and can not be further converted to a datetime value by sql server. You have to specify a size for varchar (>= to the max number of chars in your date format) in order to avoid the problem.

weefeet

ASKER

I can get it working through SQL using the following command

insert into <table_name> values(162,'Data Removed','Nov 21 2002 11:04:13:030AM')

BUT I do need to get it working using VBScript.

The following code works fine in the DTS package but sets the the hundreths part of the time to 000

Function Main()

DTSDestination("id") = DTSSource("Col001")
DTSDestination("descn") = DTSSource("Col002")
DTSDestination("updatedate") = left(DTSSource("Col003"),20) &" " & mid(DTSSource("Col003"),25,2)
Main = DTSTransformStat_OK
End Function

CleanupPing

weefeet:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.

arbert

No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:

PAQ/Refund

Any objections should be posted here in the
next 4 days. After that time, the question will be closed.

Arbert
EE Cleanup Volunteer

ASKER CERTIFIED SOLUTION

modulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial