Derived Column - Date Conversion

Question

I need to be able to convert dates into a standard format from two different possible formats depending on the file being imported.
'Fri 07/17/2009' or just '07/17/2009'.  I would like to have it in this format: YYYY:MM:DD but am completely stuck on how to do it.

Answer 1

Sorry, mixed that up a bit.  The formats are 'Fri 07/17/2009' and '17/07/2009'

Answer 2

hi,
great explaination by Todd. thanks Todd for this.
Check here: Converting Strings to Dates in the Derived Column Component

thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 3

I can't seem to find that example.

Answer 4

try thsi in a script

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Option Explicit On

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)
        '
        ' Add your code here
        '
       
        Try

            If String.IsNullOrEmpty(Row.CallDate.ToString) Then
                Throw New Exception("Error in the Date Column")
            End If

            Dim DateFormat As String
            Dim strYYYY As String
            Dim strMM As String
            Dim strDD As String
            Dim strLen As Integer
            Dim StartLen As Integer
            Dim EndLen As Integer
            Dim PosFirstSlash As Integer
            Dim PosSecondSlash As Integer

            'MsgBox(Row.CallDate + " =  main date")
            strLen = Row.CallDate.Length
            StartLen = strLen - 4
            EndLen = strLen

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''
            PosFirstSlash = Row.CallDate.IndexOf("/")
            PosSecondSlash = Row.CallDate.LastIndexOf("/")
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            '' for the YEAR
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            'strYYYY = Row.CallDate.Substring(StartLen, 4)
            strYYYY = Row.CallDate.Substring(PosSecondSlash + 1, 4)
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            '' month
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            strMM = Row.CallDate.Substring(0, PosFirstSlash)
            strMM = Trim(strMM) '' some cases we have (Space number _1)
            If strMM.Length = 1 Then
                strMM = "0" & strMM
            End If
            'MsgBox(strMM + "str MM")
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            '' Dat
            ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            strDD = Row.CallDate.Substring(PosFirstSlash + 1, PosSecondSlash - 1 - PosFirstSlash)
            strDD = Trim(strDD) '' some cases we have (Space number _1)
            If strDD.Length = 1 Then
                strDD = "0" & strDD
            End If
            'MsgBox(strDD + "str DD")
            ' Throw New Exception("Error in the Date format")

            DateFormat = strYYYY & "-" & strMM & "-" & strDD
            ' Redirect rows toward valid row output           
            'MsgBox(DateFormat)

            If Not IsDate(DateFormat) Then
                '''' Redirect rows toward valid row output
                Throw New Exception("Error in the Date format")
            End If

            Row.CallDate = DateFormat
            Row.DirectRowToValidRows()

        Catch ex As Exception
            ' Capture error description           
            Row.ErrorDescription = ex.Message.ToString
            'MsgBox(ex.Message.ToString)
            'MsgBox(Row.CallDate + " =  main date")
            ' Redirect rows toward error row output           
            Row.DirectRowToErrorRows()
        End Try

       

    End Sub

End Class

Answer 5

Sorry, mixed that up a bit.  The formats are 'Fri 07/17/2009' and '17/07/2009'

use derived column,

please check the following expression. let me know your result.(its rough expression)


SUBSTRING([date string], 4,10), 4, 2 + "/" SUBSTRING([date string], 4,10), 1, 2) + "/" + SUBSTRING([date string], 4,10), 6, 4)


Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 6

Hi that a very very code i like it
as you know most of these type of convertion date is within CSV and text files
in lost of CSV i have seen that the have some extra space so i think that they need to trim of the empty space
plus what happend if they enter 1/1/2009 will your code catch that ?

and correct me if i am wrong i think the + is missing in your code
 string], 4,10)7, 4) + "/" SUBSTRING([date string], 
after the "/" mst be a + sign , am i right ?
sincerely
sh

Answer 7

you will have to modify my code to remove the week days
in the

strMM = Row.CallDate.Substring(0, PosFirstSlash)

the 0 must be a (PosFirstSlash-2 ) 
strMM = Row.CallDate.Substring(PosFirstSlash - 2 , PosFirstSlash)

and if you have mm/dd/yy and dd/mm/yy my question to you is which on is date and which one is month in my example 01/02/2009 or 07/08/2009
this will be your issue that you have to fix as a contract of data format with your enduser/customer

thanks

 Don’t forget to mark the post(s) that answered your question , thanks

sh

Answer 8

I tried the Substring command, it wouldn't accept it, huge error string.

The script looks impressive but my knowledge with scripting is extremely limited, I am not sure how to implement it other than dumping it into a script component.  I'm not sure if I need to modify or add anything for my purposes.

The second format possibility seems to be in DD/MM/YYYY.

Thanks for all the help.

Answer 9

I tried the Substring command, it wouldn't accept it, huge error string.

The script looks impressive but my knowledge with scripting is extremely limited, I am not sure how to implement it other than dumping it into a script component.  I'm not sure if I need to modify or add anything for my purposes.

The second format possibility seems to be in DD/MM/YYYY.

Thanks for all the help.

that was a rough expression.

SUBSTRING(SUBSTRING([date string], 4,10), 4, 2) + "/" SUBSTRING(SUBSTRING([date string], 4,10), 1, 2)) + "/" + SUBSTRING(SUBSTRING([date string], 4,10), 6, 4)

check this. let me know your observation.  :)

thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 10

My script is been working for over 2.5 years
main problems in date convertion comes from CSV or Txt files , story short you have to Fix the dat format with your customer , the reson that you can bring to hte table is the example that i gave you in my prevoius Post
anyways
i can help you and send you a small package if you like
just send me your source file
[e-mail]

---

----------------------------------------------- Sincerely SH Please kindly don’t forget to mark the post(s) that answered your question and/or you may want to vote for.

Answer 11

It still error's out.  For [DateString], am I entering the column name?  I can't enter a specific string because it changes depending on the file being imported through the loop

Answer 12

What is your file type? CSV, txt, Excel ?
and please type some data of the Date within your file

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question or vote for the post

Answer 13

The data is imported from text files in no particular format.  I have created a few derived column's to strip the data that I need.  After the second derived column, the format for the date column is exactly 'Mon 05/21/2009' or '21/05/2009'.  I can include the text file if you need it but I don't think it would help as the data is no longer in that format when I want to do the date conversion.

Thanks again

Answer 14

The data is imported from text files in no particular format.  I have created a few derived column's to strip the data that I need.  After the second derived column, the format for the date column is exactly 'Mon 05/21/2009' or '21/05/2009'.  I can include the text file if you need it but I don't think it would help as the data is no longer in that format when I want to do the date conversion.

Thanks again

hi,
are you getting symbol "  '  " in your input string? 

please try after changing the some starting and end numbers in the previous expression. 

thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 15

 Hi ETL ETL
Can you tell me how is you code working while the date format is mm/dd/yyyy and dd/mm/yyyy and maybe Fri mm/dd/yyyy, will it know the difference?
will it pick up 3 format and convert them into YYYY-mm-dd???
thanks

---

Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question or vote for the post

Answer 16

Sorry, no the ' are not included.  It is just the date itself.  I will try a few things with your previous example.  Would I not need to configure some sort of CASE statement though?  To handle which of the two formats the date is in?

Answer 17

Sorry, mixed that up a bit.  The formats are 'Fri 07/17/2009' and '17/07/2009'

you need to put a conditional split on above column. check the input data length for the column and split the date. then use my expression for ---'Fri 07/17/2009' -- check the todd link "Converting Strings to Dates in the Derived Column Component" for case 2.

thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 18

Okay, thanks, I configured a Conditional Split (learning something new every day :))  and setup the case for the first example.  When I add your code, its still red and errors out.  This is what I am entering
SUBSTRING(SUBSTRING(ImageDeployDate, 4,10), 4, 2) + "/" SUBSTRING(SUBSTRING(ImageDeployDate, 4,10), 1, 2)) + "/" + SUBSTRING(SUBSTRING(ImageDeployDate, 4,10), 6, 4)

ImageDeployDate being the column title.

Answer 19

You're just missing a "+" after the first slash "/"...

---

Answer 20

I was hoping it was that easy.  I tried adding a +, still having the same problem....

Answer 21

Found it :)  An extra )

Answer 22

Sorry for all the questions, fairly new to this.

If I have a conditional Split, two separate cases, how do I go about linking them to the OLE DB Destination object?  It only allows one input?

Answer 23

Sorry for all the questions, fairly new to this.

If I have a conditional Split, two separate cases, how do I go about linking them to the OLE DB Destination object?  It only allows one input?

use the "union all" transformation to consolidate the output and connect to OLE DB destination.

Thanks- 

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 24

Sorry for all the questions, fairly new to this.

we all are here to assist  you Mr. 

:)

keep asking the doubts as somebody says:

"He who asks is a fool for five minutes, but he who does not ask remains a fool forever"

thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 25

Perfect, thanks.  It seems to somewhat work, the only problem is the format it is coming back in: /1/0/4/20, /0/0/6/20 etc

Answer 26

use this.

SUBSTRING(SUBSTRING(ImageDeployDate, 5,10), 4, 2) + "/" + SUBSTRING(SUBSTRING(ImageDeployDate, 5,10), 1, 2)) + "/" + SUBSTRING(SUBSTRING(ImageDeployDate, 5,10), 6, 4)

thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 27

I got it working for the one date format, i just noticed there are 3 formats altogether.  I setup a 3-way conditional statement and trying to use a FINDSTRING based on whether the date contains a "-" or a "/" but it does not appear to work.  FINDSTRING(ImageDeployDate,"/",1) == 3 I'm looking for a '/' in the 3rd character but it does not appear to be working.  String[DT_STR] is the datatype the date is in, I read that FINDSTRING only works on DT_WSTR.  How could I go about converting the string?  I tried adding another derived column and casting to a DT_WSTR but it still does not work.

Thanks,

Answer 28

Try this:

FINDSTRING((DT_WSTR, 20)[ImageDeployDate], "/", 1) == 3

---

Answer 29

I tried that, it accepts the string but does not process the file when running the project.  It is not detecting the field contains a /.

Answer 30

I'm just catching up on what you're trying to do here - does that column date-typed?  If so, it's likely that SSIS will be converting that to the ISO/SQL standard of YYYY-MM-DD, using dashes instead of slashes.  But more importantly - if that column is date-typed, there's no need to make that check.  What are you really checking for?  NULL?

---

Answer 31

I am basically trying to convert 3 different possible date formats into a standard format.  The data is stored as DT_STR and not a particular date format. In this particular situation, the format looks like 03/07/2009

Answer 32

That doesn't make any sense.  To troubleshoot, add a Derived Column component and add a new column with this expression:

FINDSTRING((DT_WSTR, 20)[ImageDeployDate], "/", 1)

Put a data viewer after it - what number is it filled with?

---