CODEWITHSUNDEEP
ssis
António Magalhaes
António Magalhaes

Reputation: 21

SSIS using derived column to convert data from mm/d/yyyy to dd/mm/yyyy

I have a SSIS package that inserts data from csv into SQL database. This file has a data column that the format is different from SQL. I am getting error because the SSIS says that the data can not be convert.

The datacolumn is like this 11/9/2022 12:00:00 AM this data is related to 9th of november.

I need to use derived column to convert this data into dd/mm/yyyy. I was trying using SUBTRING but I noticed that as firts character are the month we do not have the same number of character before the first / and the same for day.

I was wondering if I could try something like that

SUBSTRING([Dt],FINDSTRING("/",[Dt],1) + 1,FINDSTRING("/",[Dt],2) - 1) + "/" + LEFT([Dt],FINDSTRING("/",[Dt],1) - 1) + "/" + SUBSTRING([Dt],FINDSTRING("/",[Dt],2) + 1,4)

But I am getting the same error

How can I do to convert 11/9/2022 into this 09/11/2022 (dd/mm/yyyy) using derived column?

Upvotes: 1

Views: 81

Answers (2)

KeithL
KeithL

Reputation: 5594

This stuff is always easier in c#. Assuming you have a string, then use script componentas follows:

var newCol = DateTime.ParseExact(row.ColumnName
              , "MM/dd/yyyy hh:mm:ss tt"
              , System.Globalization.CultureInfo.InvariantCulture)
                   .ToString("dd/MM/yyyy");

All that being said. It is not great practice to carry strings around that represent dates. Once properly loaded into a date, keep it as a date until the final presentation layer.

Upvotes: 0

HSS
HSS

Reputation: 176

You were on the right path, but had the wrong syntax for FINDSTRING - the string to search comes first.

After a bit of trial and error I came up with this:

((FINDSTRING([Dt],"/",2) - FINDSTRING([Dt],"/",1)-1)==1?"0":"") + SUBSTRING ([Dt],FINDSTRING([Dt],"/",1)+1, FINDSTRING([Dt],"/",2) - FINDSTRING([Dt],"/",1)-1) + "/" +  (FINDSTRING([Dt],"/",1)==2?"0":"") + SUBSTRING ([Dt],1,FINDSTRING([Dt],"/",1)-1) + "/" +  SUBSTRING ([Dt],FINDSTRING([Dt],"/",2)+1,4)

SSIS functions can't be multi-line so the above is on 1 line, this is over multiples lines for ease of reading

((FINDSTRING([Dt],"/",2) - FINDSTRING([Dt],"/",1)-1)==1?"0":"") +
SUBSTRING ([Dt],FINDSTRING([Dt],"/",1)+1, FINDSTRING([Dt],"/",2) - FINDSTRING([Dt],"/",1)-1) + "/" + 
(FINDSTRING([Dt],"/",1)==2?"0":"") + 
SUBSTRING ([Dt],1,FINDSTRING([Dt],"/",1)-1) + "/" + 
SUBSTRING ([Dt],FINDSTRING([Dt],"/",2)+1,4)

The 1st and 3rd lines are to handle if the day or month is single number, and adds a 0 of it is.

Upvotes: 1

Related Questions