Error Casting String to Date

Question

Error Casting String to Date

huskerwendy

Hall of Fame

Points: 3633
More actions
October 24, 2007 at 2:19 pm

#153918

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jon Russell Hall of Fame Points: 3048 More actions · Answer 1

Wendy,

Are you working with SSIS? I do not have an exact answer for you on this, but I can offer some general experience dealing with casting dates.

I have limited experience using the DT_DBDate member and therefore, cannot tell you if your syntax is correct. I will assume the that your use of "(DT_DBDATE)(SUBSTRING(@[User::FileName] , 1,4)" is correct, however, the statement looks like you are trying cast a string as an integer. Perhaps you need to cast each of the SUBSTRINGs as an integer (example: CAST(SUBSTRING(VarName,1,4) as int), or something like that).

Also, I have spent a lot of time trying to fix similar situations only to find that there was one record, or in your case a file name, with a missing or unexpected value (example: a 13 or a NULL for a month value). Are you certain the values in the file name are correct?

Regards,

Jon

huskerwendy Hall of Fame Points: 3633 More actions · Answer 2

Hi Jon,

I am using SSIS. Thanks for your reply, but I don't understand what you mean about the statement looks like I'm trying to cast a string as an int. If you mean to try casting to an int, then a date, I just tried adding the (DT_I4) cast to cast to an int, but then I get an error that I can't cast DT_I8 to DT_DBDATE. Also I only have eight files in the directory and they all have the same filename in the correct date format.

I've also tried Googling how to do this and it appears that I'm doing it as the examples that I've found show, so I must be missing something small......I just don't know what it is and it's driving me crazy! 🙂

I appreciate your help.

Thanks,

Wendy

Wendy Schuman

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

running a cast the the string as is returns a datetime value - why are you throwing in the dashes?

e.g.

CAST('20071022' as datetime)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

huskerwendy Hall of Fame Points: 3633 More actions · Answer 4

I tried it like this as well:

(DT_DBDATE)(SUBSTRING(@[User::FileName] , 1,4) + SUBSTRING(@[User::FileName] , 5,2) +SUBSTRING( @[User::FileName] , 7,2))

and I still received the error:

Error code 0x80020005 occurred attempting to convert from data type DT_WSTR to data type DT_DBDATE.

Casting expression "(SUBSTRING(@[User::FileName],1,4) + SUBSTRING(@[User::FileName],5,2) + SUBSTRING(@[User::FileName],7,2))" from data type "DT_WSTR" to data type "DT_DBDATE" failed with error code 0xC00470C2.

Wendy Schuman

huskerwendy Hall of Fame Points: 3633 More actions · Answer 5

Okay, I feel very silly, but figured out what the problem was. I was expecting just the file name to be in the FileName variable from the foreach loop container. Actually, the variable for the file name also contained the path to the file name. Once I accounted for the full path, the Substring function worked correctly to convert the string to the date.

Wendy Schuman