CODEWITHSUNDEEP
sql-server-2008ssis
user1090847
user1090847

Reputation:

SSIS Convert string to DateTime dd/mm/yyy hh:mm:ss

I want to convert a string like "2152012 101946" using a derived column in SSIS.

The output should be like "21/05/2012 10:19:46" to fit into a [DateTime] SQL Server 2008 field

Thanks!

Upvotes: 0

Views: 8735

Answers (2)

bstawski
bstawski

Reputation: 1

DT_DBTIMESTAMP data types must be in the following format for proper conversion:

YYYY-MM-DD HH:MM:SS

Using the date & time "2012-07-30 02:03:10" as an example, your derived column would appear as:

(DT_DBTIMESTAMP)(SUBSTRING([column],1,4) + "-" + SUBSTRING([column],5,2) + "-" + SUBSTRING([column],7,2) + " " + SUBSTRING([column],9,2) + ":" + SUBSTRING([column],11,2) + ":" + SUBSTRING([column],13,2))

The resulting output column would appear as:

2012-07-30 02:03:10.000

Recall that any missing values within a given date can be concatenated with the date values within your column. For example, using the string value "2152012 101946", your derived column expression would be written as:

(DT_DBTIMESTAMP)(SUBSTRING([column],4,4) + "- 0" + SUBSTRING([column],3,1) + "-" + SUBSTRING([column],1,2) + " " + SUBSTRING([column],9,2) + ":" + SUBSTRING([column],11,2) + ":" + SUBSTRING([column],13,2) + ".000")

Taking this one step further, since dates in string format often are not clean, you might consider writing a conditional statement which checks the length of a value prior to concatenating & converting the values. For example, if 2012/09/30 11:59pm and 2012/10/01 11:59pm are represented as strings in a column where leading zeros are not present, the strings may appear as:

"2012930 115959" "20121001 115959"

To account for leading zeros in the month, an if-then-else expression could be incorporated such that:

LEN(column) = 14 ? (DT_DBTIMESTAMP)(SUBSTRING([column],1,4) + "- 0" + SUBSTRING([column],5,1) + "-" + SUBSTRING([column],6,2) + " " + SUBSTRING([column],9,2) + ":" + SUBSTRING([column],11,2) + ":" + SUBSTRING([column],13,2)) : (DT_DBTIMESTAMP)(SUBSTRING([column],1,4) + "-" + SUBSTRING([column],5,2) + "-" + SUBSTRING([column],7,2) + " " + SUBSTRING([column],10,2) + ":" + SUBSTRING([column],12,2) + ":" + SUBSTRING([column],14,2))

Note that the above expression does not take values that have a length less than 14 characters or greater than 15 characters into account. In this case, you could expand the above if-then-else expression to nest additional expressions for varying lengths.

Upvotes: 0

praveen
praveen

Reputation: 12271

You should think about enriching your date time data . You need to have a proper format like

YYYYMMDD HH:MM::SS

or something similar to it . You just can't have

YYYYMDD HH:MM:SS

If you have your data in the correct format DDMMYYY HH:MM:SS then you can use the below expression in derived column 

LEN(column) == 0 ? NULL(DT_DBTIMESTAMP) :
(DT_DBTIMESTAMP)(substring(column,1,2) + "-" + substring(column,3,2) + "-" +
substring(column,5,4) + " " + substring(column,10,2) + ":" substring(column,12,2)+ ":"
+ substring(column,14,2))

Upvotes: 2

Related Questions