SQL Server 2000 DTS datetime

Posted on 2003-02-19
Medium Priority
Last Modified: 2013-11-30
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
Question by:weefeet
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 23

Expert Comment

ID: 7982486
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:
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)
SET @pstrDate = 'Nov 21 2002 11:04:13:030AM'
INSERT MyTable (162,'Data Removed', @pstrDate)

It is an annoying problem.

Expert Comment

ID: 7984075
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.  

Author Comment

ID: 7984100
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!


Expert Comment

ID: 9276561
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 
Post your closing recommendations!  No comment means you don't care.
LVL 34

Expert Comment

ID: 10910473
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:


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

EE Cleanup Volunteer

Accepted Solution

modulo earned 0 total points
ID: 10970985
PAQed, with points refunded (75)

Community Support Moderator

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question