0

I need to convert a date field which is in varchar format (DDMMYYYY) to DateTime using .Net. I have tried a number of solutions but all in vain. My source for that field is a flat file and my destination is a table. I am using the script in SSIS package.

Below is the sample:

The column SampleDate is in the ddmmyyyy format. I am converting it to MM/dd/YYYY in order to convert it to datetime format.

Dim retString1 As String
Dim slash As Char = ChrW(&H2F)
retString1 = Row.SampleDate.Substring(2, 2) & "/"
Dim retString2 As String
retString2 = Row.SampleDate.Substring(0, 2) & "/"
Dim retString3 As String
retString3 = Row.SampleDate.Substring(4, 4)
Dim retString4 As String
retString4 = retString1 & retString2 & retString3

Dim fromDt As Date = Convert.ToDateTime(retString4)
fromDt = Row.SampleDate

P.S. : I am not looking for SQL Query for the conversion so before commenting, please read the whole question thoroughly. I am looking for a VB code and not a SQL code.

user692942
  • 16,398
  • 7
  • 76
  • 175
Anant Vakharia
  • 25
  • 6
  • You should really check that the answer has not already been provided before positing a new question: http://stackoverflow.com/questions/21833719/convert-flatfile-column-to-datetime – BIDeveloper Jul 05 '16 at 10:46
  • Dear BIDeveloper, if you think the answer is already there, let me know. Trust me I have tried everything, but my VB code is not converting the DDMMYYYY to YYYY-MM-DD HH:MM:SS format. – Anant Vakharia Jul 05 '16 at 11:03
  • While I don't doubt that the question of how to convert a string (from a flat/text file) to a variable of (sub) type Date has been asked before, the answer "use convert from your sql server" is wrong. – Ekkehard.Horner Jul 05 '16 at 11:04
  • 2
    That is not VBScript is looks more like VB.Net especially with the use of `Convert.ToDateTime()` method. – user692942 Jul 05 '16 at 11:24
  • 3
    Possible duplicate of [Convert a string to a datetime](http://stackoverflow.com/questions/8634568/convert-a-string-to-a-datetime) – BIDeveloper Jul 05 '16 at 11:28
  • Now that the question has been corrected to reflect what is actually required - it is a duplicate of http://stackoverflow.com/questions/8634568/convert-a-string-to-a-datetime – BIDeveloper Jul 05 '16 at 11:29
  • 1
    If this is vb.net and not vbscript, then look up [Date.ParseExact](https://msdn.microsoft.com/en-us/library/w2sa9yss(v=vs.110).aspx). – the_lotus Jul 05 '16 at 12:51

2 Answers2

1

Just flip fromdt = Row.SampleDate

to

Row.SampleDate = fromdt

And your script should work but see below to simplify some if you want.

HOW TO DO IT WITHOUT A SCRIPT --see below for script method

You can make this a lot simpler for yourself if you use a Derived Column transformation instead! The data conversion transformation doesn't work because it doesn't allow you to manipulate the value of the column, but rather only the data type. But in the derived column you can cut up the string and cast the result as a date.

So Source to Derived Column to Destination in your data flow.

enter image description here

In the derived column your add a new column and use the following as the expression (replacing DateString with the name of your column containing the date in ddMMyyyy format)

Then use this as the Expression:

(DT_DATE)(SUBSTRING(DateString,3,2) + "/" + (LEFT(DateString,2) + "/" + RIGHT(DateString,4)))

The (dt_date) can be switched for another date format if you wish and that type cast will automatically set the Data Type on the Derived Column. Then in your destination map the new derived column to the destination column instead of your original column from your source.

enter image description here

That's it your done, no scripts.

HOW TO DO IT VIA A SCRIPT

To address the route if you wanted to stay with a script and perhaps inject some additional logic rather than simple conversion you can do it through a transformation script component in a your data flow task. It will take place of the derived column transformation above but essentially will do the same thing only through a script. I am putting steps in here that I am guessing you already know some of in case someone else stumbles on the post.

  • Add the script component when you do choose "transformation" and connect it with you source
  • Open Script component and go to the "Inputs and Outputs" section and add an output to hold the new column and set the datatype. A new column is necessary because you are changing data types

enter image description here

  • go to "Input Columns" section and choose SampleDate column

enter image description here

  • go back to "Script" section and choose Visual Basic as your script language.
  • Click Edit Script to begin your coding.
  • Scroll down till you find the sub Input0_ProcessInputRow(ByVal Row... this is where you will put your code.

This code works:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    If Not Row.SampleDate_IsNull Then

        Row.DerivedDate = DateTime.ParseExact(Row.SampleDate, "ddMMyyyy", System.Globalization.CultureInfo.InvariantCulture)

    End If
End Sub

As @Shiva in comments suggested you could expand this and use TryParseExact or wrap it in a try catch block so that if parsing the date fails it would simply clear/remove the value from the record and the import will continue. I leave that up to you as handling invalid data is more a question of business requirements.

Matt
  • 13,833
  • 2
  • 16
  • 28
  • `That's it your done, no scripts. You of course can do it via a script but I don't see why would when the standard options are available to your`. Your solution assumed the "happy path" only. The source datefield is of type `varchar`. That means that it could have invalid data too. Using a script gives greater control in that you can do a `TryParse` on that field and convert to date only if that succeeds. This derived column approach would fail even if there is valid date, that is not EXACTLY in the `DDMMYYYY` format. That said, I have given a +1 for your efforts. – Shiva Jul 05 '16 at 18:33
  • 1
    @Shiva the OP clearly said 1 format, and not mention of potential validity issues. I can definitely see the reason to use script tasks for testing validity. But then the question is if invalid what do you want to do, still import? That leads to what should the value be for an invalid date? Null what if destination doesn't allow for Nulls, import will still fail.....At some point decisions of validity of data need to be made and typically I always want them to fail until I can find pattern/discover ways of correcting invalid dates rather than succeed quietly. PS I use way more complex scripts – Matt Jul 05 '16 at 18:49
  • Thanks for clarifying. It makes sense now. – Shiva Jul 05 '16 at 19:21
  • 1
    @Shiva I decided to add the script method as well. – Matt Jul 05 '16 at 20:56
  • Excellent! This makes it a complete answer now :) – Shiva Jul 05 '16 at 21:49
  • Thanks a lot Matt and Shiva. I am a newbie in SSIS and that really did help. I am also getting junk values like ABC, NR etc in my SampleDate column. How do I handle it? Does SSIS support IIF function that we have in SSRS. Or do we have IsDate equivalent function? – Anant Vakharia Jul 07 '16 at 07:29
  • Could you potentially have a delimiter problem causing the text to be in your date field? Is the text always at the end or beginning of the date? I would probably use the script task and do some string manipulation, but you can also use a conditional syntax here is a link on how to do it in expressions in ssis. https://msdn.microsoft.com/en-us/library/ms141680.aspx – Matt Jul 07 '16 at 15:53
0

To convert a string to a date in VBScript you either use CDate() which does the conversion for you but depends on the current regional settings or DateSerial():

>> s = "13041953"
>> d = DateSerial(CInt(Right(s,4)), CInt(Mid(s,3,2)), CInt(Left(s,2)))
>> WScript.Echo s, TypeName(d), d
>>
13041953 Date 13.04.1953 (german locale)
Ekkehard.Horner
  • 38,498
  • 2
  • 45
  • 96