Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

564 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