SSIS DT_DBDateTimeStamp to DD/MM/YYYY without script task (derived column)

Question

I'm trying to construct an SSIS package where one of the columns is a DT_DBDataTimeStamp format.

My problems is that I have to convert this to a DD/MM/YYYY format and I can't use a C# script.

I'm trying to use a derived column by having difficulty in getting the correct format.

I've tried Convert(varchar, Date_Column, 103) - doesn't work. I've tried DAY(Date_Column) + MONTH(Date_Column) + YEAR(Date_Column) - doesn't work.

Anybody know how I can do this?

Please post the way you attempted to use DAY()+MONTH()+YEAR(). And the exact error message you got. It does work if you do it correctly. — Tab Alleman, Sep 21 '17 at 13:28

score 0 · Accepted Answer · edited Oct 11 '18 at 04:22

0

It would need to be varchar if you want this (actually, I will use nvarchar for simplicity):

right("0" + (DT_WSTR,2) day(Date_Column), 2) + "/" +
right("0" + (DT_WSTR,2) month(Date_Column) , 2) + "/" +
(DT_WSTR,4)year(Date_Column)

edited Oct 11 '18 at 04:22

Pang

answered Sep 21 '17 at 11:57

KeithL

I can't get this to work - thanks for your input though, much appriciated – Graham Reavey Sep 21 '17 at 15:05
I forgot to cast year – KeithL Sep 21 '17 at 15:49
Thanks, after playing around this worked - much appreciated – Graham Reavey Sep 21 '17 at 19:07
1

Please don't forget to mark the answer as it will stop people from coming to answer your question. – KeithL Sep 21 '17 at 19:36

1 Answers1