Ssis Date in Derived Column as Yyyy-Mm-Dd Format

SSIS Date in derived column as yyyy-mm-dd format

SSIS Derived Column expression variant:

(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())  + "-" + 
RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2)  + 
"-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2)

SSIS - Converting unformatted strings dates to YYYY-MM-DD

This task is quite tricky since we're not only dealing with unformatted String dates but they are also not in the region netural YYYY-MM-DD format.

To fix this, we can use the follwing expression for the Derived Column:

RIGHT([mydate],4) + "/" + SUBSTRING([mydate],FINDSTRING([mydate],"/",1) + 1,FINDSTRING([mydate],"/",2) - FINDSTRING([mydate],"/",1) - 1) + "/" + SUBSTRING([mydate],1,FINDSTRING([mydate],"/",1) - 1)

The expression is quite long but what it is doing is taking the values (month, day, year) between the forward slashes / and concatenating them into a format that resembles YYYY/MM/DD that can then be converted in SSIS using a Data Conversion transformation. This avoids the error of dealing with the change in length in dates like 1/1/2000 and 10/10/2000.

The output of the derived column was named YYYYMMDD and this value was then passed into a Data Conversion transformation that has output Date Converted YYYYMMDD as seen below.

The Data Conversion task is simply doing the follwing:

SSIS 2019 - Derived Column String Date/Time MM/DD/YYYY HH:MM:SS.s to datetime2

I was able to get a working pipeline, was mostly some CAST misinterpretations within my previous code.

Derived column one: ((DT_WSTR,4)YEAR(((DT_DATE)[6]))) + "-" + RIGHT("0" + ((DT_WSTR,2)MONTH(((DT_DATE)[6]))),2) + "-" + RIGHT("0" + ((DT_WSTR,2)DAY(((DT_DATE)[6]))),2)

Derived column two: (DT_DBTIMESTAMP2,1)(SDATE + [7])

Change Date format from yyyy-mm-dd to mm/dd/yyyy Derived Column

Say your Date Column is called DateCol

You will need to create three derived columns from that column and than simply concatenate them columns something like this

derivedColumns   Expression 
year             SUBSTRING(DateCol, 1, 4)
day              RIGHT(DateCol, 2)
Month            SUBSTRING(DateCol, 6, 2)

NewDate          SUBSTRING(DateCol, 6, 2) + "/" + RIGHT(DateCol, 2)+ "/"+SUBSTRING(DateCol, 1, 4)

Your NewDate Column will be in your desired format

Convert mmddyy to YYYY-MM-DD in expression task in SSIS

Found a solution for this using the below code

"20" + RIGHT(DATE,2) + "-" + LEFT(DATE,2) + "-" + SUBSTRING(DATE,3,2)

How to convert CYYMMDD to YYYY-MM-DD in SSIS?

Suppose your CYYMMDD column is called cdate and let's call the transformed date ydate then you may need a Derived Column transformation with this expression:

LEFT(cdate,1) == "0" 
? "19" + SUBSTRING(cdate,2,2) + "-" + SUBSTRING(cdate,4,2) + "-" + RIGHT(cdate,2) 
: "20" + SUBSTRING(cdate,2,2) + "-" + SUBSTRING(cdate,4,2) + "-" + RIGHT(cdate,2)

result:

How to convert string in format yyyyMMdd to date using SSIS expression?

As per Kyle Hale suggestion, below is my answer

    SUBSTRING([DATE_DECISION_TO_REFER],1,4) + "-" +
 SUBSTRING([DATE_DECISION_TO_REFER],5,2) + "-" + SUBSTRING([DATE_DECISION_TO_REFER],7,2)

To educate you so that you will never face this issue again, this is how expression works

 Get 4 characters from DATE_DECISION_TO_REFER starting at position 1, add a dash,
 get 2 characters from DATE_DECISION_TO_REFER starting at position 5,
 add a dash then add 2 characters from DATE_DECISION_TO_REFER starting at position 7.

Hope this will help.

---

Related Topics