Derived Column - DateTimeStamp From String (dd-mmm-yy)

I have come across something strange. I was wondering anyone else had the same problem or is it me?

I have a source file as shown below:
1,30-Feb-06,3,Test
2,20-Feb-06,5,Test1

Second column is defined as String(9) in the flat file source. Then I have a Derived Column between source and Trash destination. The Derived Column adds a new column to the datd flow. The expression is as shown below :
(DT_DBTIMESTAMP)(SUBSTRING(Col2,1,2) + "/" + SUBSTRING(Col2,4,3) + "/" + SUBSTRING(Col2,8,2))

The data is loading into the database successfully, where I was expecting it to fail. I placed a data viewer and saw the following result set.

Source (Col2) Derived Col
-
30-Feb-06 06/02/1930 00:00:00 - WRONG result set
20-Feb-06 20/02/2006 00:00:00 - RIGHT result set

Can anyone repeat this problem or am I missing something? I have got SP1 and Hotfix installed.

Thanks

Sutha

This one feels like an old friend - albeit an irritating old friend.

Briefly, this is a result of routines in oleaut. Really, the oleaut library is trying to be friendly and to cast your string to a valid date. Knowing that there are different formats out there such as yy-mm-dd and dd-mm-yy it "assumes" that you have a valid date in one of these formats and therefore - to hugely oversimplify - finds the format that enables it to perform a cast to a valid date.

Note that this behaviour has been around for a long time when using a non-US local (there are developers who believe it led to some slight misalignment of the pyramids) and many applications actually rely on the behaviour, so it is difficult to change after a long time.

I would suggest that if you are likely to have invalid dates of this particular nature, that you validate them using some code before applying the cast or conversion. Not a great solution, but effective.

Donald

Deploying Reports

Hi All,
I am having a strange problem. I have created a shared data source and
multiple datasets for my reports. The multiple datasets are created to get
data for the different parameters in the report. The report works fine in
Visual Studio. But I don't see the data source listed and when I try to
deploy the report and run it on my webserver. I get the following error
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'CWPresales'.
(rsErrorOpeningConnection)
For more information about this error navigate to the report server on the
local server machine, or enable remote errors
Can anyone help me with this. I am confused on why I cannot see the
datasource listed in the solution explorer.
Thanks
ShriI am not sure what you mean by solution explorer? I use Report Manager for
this sort of thing. When you deploy the data sources get created BUT they
get created without credentials. You need to use Report Manager to view the
properties of the data source and set the credentials.
Data sources in RS2005 can either be in the same folder as your reports or
in a special folder called Data Sources. Just in case they are hidden in
List View then click on the Show Details link in Report Manager.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"formcreator" <formcreator@.discussions.microsoft.com> wrote in message
news:44C8FFDC-B340-43E5-8244-9AAEA63902D1@.microsoft.com...
> Hi All,
> I am having a strange problem. I have created a shared data source and
> multiple datasets for my reports. The multiple datasets are created to get
> data for the different parameters in the report. The report works fine in
> Visual Studio. But I don't see the data source listed and when I try to
> deploy the report and run it on my webserver. I get the following error
>
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'CWPresales'.
> (rsErrorOpeningConnection)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
> Can anyone help me with this. I am confused on why I cannot see the
> datasource listed in the solution explorer.
> Thanks
> Shri