CODEWITHSUNDEEP
ssisscript-component
user7418776
user7418776

Reputation: 57

ssis adding year to date of database with script component

i have a database with no year, only day and month (example 20-12). because of this, the database adds the year automatically. The problem with this is, that in some cases the year is ahead of time, for example 20-12-2020, while it's actually 2019.

example of the table:

order_number date_of_order (original table)
1 20-12

how it looks when i add the database in ssms:

order_number date_of_order 1 20-12-2020

What i want to do is add the right years to the day and month with scripts component. for example if the date is in the past, then make it 2019. If the date is in the future make it 2020.

script component error

Upvotes: 1

Views: 354

Answers (2)

KeithL
KeithL

Reputation: 5594

If you are already starting with a date then you can just use a derived column to do your transformation:

Use this as formula similar to the c# above:

OrderDate > GETDATE() ? DATEADD("yy",-1,OrderDate) : OrderDate

enter image description here

Upvotes: 1

KeithL
KeithL

Reputation: 5594

This will give you a path to move forward. You just need to separate the column into month and day numbers:

        //This will split out your date/month
        string OrigCol = "20-12"; //Replace this with Rows.YourColumnName
        string[] col = OrigCol.Split('-');
        int d = Int32.Parse(col[0]);
        int m = Int32.Parse(col[1]);

        DateTime dt = new DateTime(DateTime.Now.Year, m, d);
        if (dt > DateTime.Now)
            dt = dt.AddYears(-1);

        //Set your new column to dt

This code will create a date with current year, and only subtract a year if the result is in the future.

If your column is already a date (with a 2020 default) you can jump straight into the if.

        DateTime dt = Row.YourColumnName;
        if (dt > DateTime.Now)
            dt = dt.AddYears(-1);

        //Set your new column to dt

Upvotes: 1

Related Questions