Hi guys trying to convert

16JAN2014 --> 16/01/2014. Any ideas??? i'm trying to avoid the horrendous expression of ? JAN : 1 ? FEB : 2 etc. etc.

SSMS will quite happily do SELECT MONTH('16JAN2014') to get me the number of month however MONTH( "16JAN2014" ) will not work in SSIS.

hmmm... tried code but keep getting error below...

enter image description here

  • How about using -- today’s date in the format DD/MM/YYYY --> RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("d",31,GETDATE())),2) + "/" + (DT_WSTR,4)YEAR(GETDATE()) in an expression.
    – Kin Shah
    Jan 16, 2014 at 14:37
  • hi the date is a column in the dataflow (string) i need to reference that
    – jhowe
    Jan 16, 2014 at 14:55
  • 1
    How many rows do you expect to need to do this conversion upon?
    – billinkc
    Jan 16, 2014 at 14:58
  • The files i'm importing aren't MASSIVE but would like to do this on up to around 1000 rows
    – jhowe
    Jan 16, 2014 at 15:01

1 Answer 1


Use a Script Component to perform the conversion! Be sure to set your read / write variables of course! It's quite easy for use specific locale settings with the .NET framework, so a Script Component should be superior than trusting the database to correctly do the conversions for you.

Using a Script Component to Parse a DateTime Value from a String

    using System.Globalization;


    public void Main()
        String str = ( String )Dts.Variables[ "StringVariable" ].Value;

             // Good candidate for another variable, this is just an example.
        String format = "ddMMMyyyy"; 
        CultureInfo provider = CultureInfo.InvariantCulture;

        DateTime dt = DateTime.Parse( str, format, provider );
        Dts.Variables[ "DateTimeVariable" ].Value = dt;

        Dts.TaskResult = ( int )ScriptResults.Success;

And in VB, if that's How You Roll™

    Imports System.Globalization


    Public Sub Main()
            Dim str As String = Dts.Variables("StringVariable").Value
            Dim format As String = "ddMMMyyyy"
            Dim provider As CultureInfo = CultureInfo.InvariantCulture
            Dim dt As DateTime = Date.ParseExact(str, format, provider)
            Dts.Variables("DateTimeVariable").Value = dt
            Dts.TaskResult = ScriptResults.Success
    End Sub
  • Hi i'm stuck with SSIS 2005 for this can you post in VB? Thanks...
    – jhowe
    Jan 16, 2014 at 15:00
  • 1
    There you go! I haven't written a line of VB for about 4 years, so that was pretty interesting. Based on how you've described the problem, you'll probably want to use the Script Component as a transformation in a Data Task Flow and configure an output column containing the derived date.
    – Avarkx
    Jan 16, 2014 at 15:19
  • I couldn't get this to work but will mark as answer as it should work. Anyone reading with this I ended up using an expression that checks initially if field is blank. TRIM(@[Variable]) == "" ? NULL(DT_WSTR,1) : (DT_WSTR,20)((DT_DATE)(TRIM(SUBSTRING(@[Variable],1,2) + "-" + SUBSTRING(@[Variable],3,3) + "-" + RIGHT(@[Variable],4))))
    – jhowe
    Jan 17, 2014 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.