Trouble Converting from DT_WSTR to DT_DBDATE

Question

Trouble Converting from DT_WSTR to DT_DBDATE

ganteng1

Ten Centuries

Points: 1183
More actions
April 23, 2014 at 8:10 pm

#286053

Hi there,
I have a date in SSIS that is Unicode String (DT_WSTR) and format DD-MM-YYYY.
I have tried to convert this to DT_DBDATE using the data conversion task in SSIS, but get errors. The error is as follows:
Error: 0xC02020C5 at Data Flow Task, Data Conversion [806]: Data conversion failed while converting column "Derived DATE" (900) to column "Data Conversion Derived DATE" (915). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Thanks for any help provided

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ganteng1 Ten Centuries Points: 1183 More actions · Answer 1

Hi,

I tried the following, but it still didn't work when trying to return a date as DD-MM-YYYY. Please see the expression below:

(DT_DBDATE)(DT_WSTR,2)(SUBSTRING(DATE,1,2) + "-" + (DT_WSTR,2)SUBSTRING(DATE,4,2) + "-" + (DT_WSTR,4)SUBSTRING(DATE,7,4))

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

SSIS expects a specific format.

YYYY-MM-DD HH:MIS:SS

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/65e1c7d2-5410-48d9-9bb1-964ea124d0d1/how-to-convert-string-data-type-to-datetime-in-derived-column-control-in-ssis-package?forum=sqlintegrationservices

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Umer Akram SSC-Insane Points: 22209 More actions · Answer 3

Koen Verbeeck (4/24/2014)
SSIS expects a specific format.
YYYY-MM-DD HH:MIS:SS
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/65e1c7d2-5410-48d9-9bb1-964ea124d0d1/how-to-convert-string-data-type-to-datetime-in-derived-column-control-in-ssis-package?forum=sqlintegrationservices

Just a thought really, In addition to Koen respone if your source is sql server, you can perform this convert on database level. 🙂

rhythmk SSCertifiable Points: 7271 More actions · Answer 4

ganteng1 (4/23/2014)
Hi,
I tried the following, but it still didn't work when trying to return a date as DD-MM-YYYY. Please see the expression below:
(DT_DBDATE)(DT_WSTR,2)(SUBSTRING(DATE,1,2) + "-" + (DT_WSTR,2)SUBSTRING(DATE,4,2) + "-" + (DT_WSTR,4)SUBSTRING(DATE,7,4))

You are trying a wrong expression. Try this in derived column.

(DT_DBDATE)(SUBSTRING (DATE ,1,2) + "-"+ SUBSTRING( DATE ,3,2) + "-" + SUBSTRING(DATE ,5,4))

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

twin.devil (4/24/2014)
Koen Verbeeck (4/24/2014)
SSIS expects a specific format.
YYYY-MM-DD HH:MIS:SS
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/65e1c7d2-5410-48d9-9bb1-964ea124d0d1/how-to-convert-string-data-type-to-datetime-in-derived-column-control-in-ssis-package?forum=sqlintegrationservices
Just a thought really, In addition to Koen respone if your source is sql server, you can perform this convert on database level. 🙂

+1 Data manipulation is way easier in SQL Server than in SSIS.

If your destination is SQL Server, you can leave the string and let SQL Server do an implicit conversion (make sure you are using the right locale though).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ganteng1 Ten Centuries Points: 1183 More actions · Answer 6

Thanks for the advice. I will try to do the data manipulation in SQL