DT_DBDATE date to YYYYMMDD in dervived column transformation

user_14945868 0
Viewed 777

I want to convert my column of transdate into YYYYMMDD format. In sql server I use this query

SELECT FORMAT([transdate],'yyyyMMdd')

It is running perfectly but I want to do this step using the derived column in SSIS please help. The data type is DT_DBDATE.

I used the following query but it is not working.

(DT_DBDATE)((YEAR(transdate) * 10000) + (MONTH(transdate) * 100) + DAY(transdate))

The reason of converting date into string format is basically i created a date table and i extracted this date from trans_date. Now i have to make dateid from that date That's why I converted it into int for further work especially for denormalization step.
etl ssis datetime sql-server sql
3 Answers

Try to use data conversion task to convert you date column with datatype DT_DBDATE to DT_WSTR, then use the converted column in the expresion to get the result

(DT_DBDATE)(SUBSTRING([ConvertedDate], 1, 4) + SUBSTRING([ConvertedDate], 6, 2)  + SUBSTRING([ConvertedDate], 9, 2))
user_122516870

When converting DT_DBDATE to a string, the date value is formatted as yyyy-MM-dd. You can simply remove the dashes - using a replace function as following:

REPLACE((DT_WSTR,50)(DT_DBDATE)[ConvertedDate],"-","")

For more information you can refer to the official SSIS documentation:

user_70312300

  1. Drag the derived column and use this expression: (YEAR(input_datetime_col) * 10000) + (MONTH(input_datetime_col) * 100) + DAY(input_datetime_col) and add as a new column.

  2. Drag the data conversion, and convert it into four-byte signed integer [DT_I4]

  3. Map this new column to your destination.

user_149458680
Related