Error Casting String to Date

huskerwendy


I have a Foreach loop container that I'm using to loop through a directory and copy files to a new directory. The file name contains the date and I need to compare the value to the current date to determine if the file should moved. I'm having a problem converting the string to a date.

I have a FileName variable of type string that is in this form: 200710221200_FileName_7001.log

I have a FileDate variable of type datetime that I'm trying to use the following expression on:
(DT_DBDATE)(SUBSTRING(@[User::FileName] , 1,4) +"-"+ SUBSTRING(@[User::FileName] , 5,2) +"-"+SUBSTRING( @[User::FileName] , 7,2))

Which is giving me the following error:

The expression "(DT_DBDATE)(SUBSTRING(@[User::FileName] , 1,4) +"-"+ SUBSTRING(@[User::FileName] , 5,2) +"-"+SUBSTRING( @[User::FileName] , 7,2))" has a result type of "DT_DBDATE", which cannot be converted to a supported type.

From the help files, it appears to me that the DT_DBDATE cast uses the form of yyyy-mm-dd. I'm not sure what I'm doing wrong. Any help is greatly appreciated.

Thanks!


Wendy Schuman

Rafael Salas


I did something similar in thsi post

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

See if you can get any help from there.

jwelch

The following expression is working fine for me - not sure why it's not working for you.

Code Block

(DT_DBDATE)(SUBSTRING("200710221200_FileName_7001.log",1,4) + "-" + SUBSTRING("200710221200_FileName_7001.log",5,2) + "-" + SUBSTRING("200710221200_FileName_7001.log",7,2))

What service pack are you using

huskerwendy

I copied and pasted your code into my Expression Builder dialog box and received the following error:

The expression "(DT_DBDATE)(SUBSTRING("200710221200_FileName_7001.log",1,4) + "-" + SUBSTRING("200710221200_FileName_7001.log",5,2) + "-" + SUBSTRING("200710221200_FileName_7001.log",7,2))" has a result type of "DT_DBDATE", which cannot be converted to a supported type.

We are using:
Microsoft Visual Studio 2005
Version 8.0.50727.762 (SP.050727-7600)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: Professional
Microsoft Visual Studio 2005 Professional Edition - ENU Service Pack 1 (KB926601)

I thought we were on the most recent SP. Is there a new one

Thanks for your help

huskerwendy

Hi Rafael,

I did originally go to your blog to find out how to do this and it was very helpful information! Thanks for posting it. I just can't seem to get the cast to work correctly and it's really driving me nuts because it seems as though it should be very easy. Do you have any other ideas as to why it's not working

Thanks!

jwelch

In the list of installed product check the version of SSIS. It should be Version 9.00.3042.00 if you are on SP2.

Rafael Salas

Code Block

(DT_DATE)(SUBSTRING("200710221200_FileName_7001.log",5,2) + "-" + SUBSTRING("200710221200_FileName_7001.log",7,2)+"-"+SUBSTRING("200710221200_FileName_7001.log",1,4))

This one worked for me. I had to make 2 changes:

1. Cast to DT_DATE,

2. Put year part at the end

BTW, this was in a machine running RTM (no SP installed)

huskerwendy

Hi Rafael,

I was able to use that code in the Expression Builder dialog box and it evaluated successfully. However, when I executed my package I received this error:

Error: 0xC00470C2 at Package: Error code 0x80020005 occurred attempting to convert from data type DT_WSTR to data type DT_DATE.
Error: 0xC00470C4 at Package: Casting expression "(SUBSTRING(@[User::FileName],5,2) + "-" + SUBSTRING(@[User::FileName],7,2) + "-" + SUBSTRING(@[User::FileName],1,4))" from data type "DT_WSTR" to data type "DT_DATE" failed with error code 0xC00470C2.
Error: 0xC0019004 at Package: The expression for variable "FileDate" failed evaluation. There was an error in the expression.


My expression is:
(DT_DATE)(SUBSTRING( @[User::FileName] ,5,2) + "-" + SUBSTRING( @[User::FileName],7,2)+"-"+SUBSTRING( @[User::FileName],1,4))

Could the problem possibly be because of the version we're running

jwelch

Have you checked the value of User::FileName to make sure it actually contains what you are expecting You can check it by setting a breakpoint on a control flow task, and then looking in the locals window under variables when the breakpoint is hit.

Richard Hathaway

Just a thought: try the original coding (yyyy-mm-dd) but use '\' instead of '-' (yyyy/mm/dd)

huskerwendy

Thank you so much for your help on this. I feel very silly because I was just expecting the file name in the variable, but the variable also included the path to the file. Once I started using the correct positions for my substring function everything works fine.

Thanks so much to all who posted replies.