Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 Jan 16 at 14:37
    
hi the date is a column in the dataflow (string) i need to reference that –  jhowe Jan 16 at 14:55
1  
How many rows do you expect to need to do this conversion upon? –  billinkc Jan 16 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 at 15:01

1 Answer 1

up vote 5 down vote accepted

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
share|improve this answer
    
Hi i'm stuck with SSIS 2005 for this can you post in VB? Thanks... –  jhowe Jan 16 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 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 at 13:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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