Date conversions in script component

Question

Date conversions in script component

stricknyn

SSCertifiable

Points: 6560
More actions
January 19, 2011 at 9:14 am

#388672

Hello all,
I have a script component that has Option Strict ON. I am trying to convert a string to a date and nothing seems to work. Row.CLOSEDDATE is a string in the format of: "yyyy-MM-dd" (ie: 2008-09-01) I've tried DirectCast, Ctype, Date.Parse and all of these return errors:
Dim test As Date = DirectCast(Row.CLOSEDDATE, DateTime) 'Error is: Value of type 'String' cannot be converted to 'Date'
Dim test As Date = Ctype(Row.CLOSEDDATE, DateTime) 'Error is: Conversion from string to 'Date' is not valid
Dim test As Date = Date.Parse(Row.CLOSEDDATE.ToString) 'Error is: Object reference not set to an instance of an object
I know i can break the string apart and build a date, but it just seems like I should be able to just convert it. Anyone know what I should be using to convert?
Thanks,
Strick

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Can you post the entire code block? The only error I can duplicate is the DirectCast error. Both CTYPE and Date.Parse are working in my tests.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

stricknyn SSCertifiable Points: 6560 More actions · Answer 2

Jack Corbett (1/19/2011)
Can you post the entire code block? The only error I can duplicate is the DirectCast error. Both CTYPE and Date.Parse are working in my tests.

Hi, thanks for your response.

Actually this is the entire code block with the exception of the beginning and ending Sub. But I'll post that as well:

Public Overrides Sub InputData_ProcessInputRow(ByVal Row As InputDataBuffer)

Dim test As Date = DirectCast(Row.CLOSEDDATE, DateTime) 'Error is: Value of type 'String' cannot be converted to 'Date'

Dim test2 As Date = Ctype(Row.CLOSEDDATE, DateTime) 'Error is: Conversion from string to 'Date' is not valid

Dim test3 As Date = Date.Parse(Row.CLOSEDDATE.ToString) 'Error is: Object reference not set to an instance of an object

End Sub

You most likely tried to reproduce the error using either a script task or a quick VB console app. I tried these as well and it works fine with the exception of the DIRECTCAST error (Same issue you had in trying to find error). In order to reproduce these errors you must use the script component. That is what makes this confusing. This works in a script task or VB console app but not in a script component.

Thanks,

Strick

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

No I'm using a script component, not a script task, within a dataflow in SSIS 2005. Here's the code:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'

' Add your code here

'

Dim test As Date = Date.Parse(Row.birthdate.ToString)

'Dim test1 As Date = DirectCast(Row.birthdate.ToString, Date)

Dim test2 As Date = CType(Row.birthdate, Date)

Row.Column = test

End Sub

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

stricknyn SSCertifiable Points: 6560 More actions · Answer 4

Wow really? Is your birthdate column in "2008-09-01" format? Also they are in DT_WSTR data type when they come in.

Thanks for your help,

Strick

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Yes the string is in the format yyyy-mm-dd. I am using an ole db source with a query that is converting a date to nchar(10) (was char(10)) using CONVERT(nchar(10), date_column, 112). The convert with the 112 format code returns an iso date with the time and the truncation to 10 characters removes the time portion.

It worked with DT_STR and is now working with DT_WSTR. I cannot duplicate.

What version of SSIS are you using? When I do Help -> About in BIDS and select SQL Server Integration Services in the installed product window I get 9.00.4035.00.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

stricknyn SSCertifiable Points: 6560 More actions · Answer 6

stricknyn

SSCertifiable

Points: 6560

January 19, 2011 at 12:30 pm

#1275509

I'm using SSIS 2008

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

That's probably why I can't duplicate because I have 2005 at work and you posted in a 2205 forum so I worked on the assumption you were using 2005.

Okay, I just ran it in 2008, Version 10.0.2531.0, with no errors. Other than the one with DirectCast.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

COZYROC One Orange Chip Points: 28499 More actions · Answer 8

The error you receive for the last test you made makes me think you have NULL input value and you are not checking for it. Try the following code:

Dim test As Date

If Not String.IsNullOrEmpty(Row.CLOSEDDATE) Then

test = Date.Parse(Row.CLOSEDDATE)

End If

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/