| Author |
Topic |
|
2fire
Starting Member
9 Posts |
 Posted - 2013-04-22 : 18:52:52
|
I am attempting to import numeric data from an AS400 system and convert to a date.
I am using a Data Conversion task to convert the number to a string(DT_STR).
Input Colum = HTDTEP and Output Alias = CNV_HTDTEP.
Then a Derived Column task converts the string to a date using this formula -
Derived Column Name = CNV_TRANDATE
Expression = (DT_DBDATE)(SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,1,2) + "/" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,3,2) + "/" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,5,2))
From this I map CNV_TRANDATE to a field in the SQL table with a DataType = Date.
When I run the script this error is reported -
The column "CNV_TRANDATE" can't be inserted because the conversion between types DT_DBDATE and DT_WSTR is not supported.
I am unsure why this message occurs and am wondering if anyone has a suggestion? Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-23 : 13:03:33
|
whats the format of date values coming in CNV_HTDTEP
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
 |
|
|
2fire
Starting Member
9 Posts |
Posted - 2013-04-25 : 10:58:46
|
Thanks for the reply!
Data format for CNV_HTDTEP is a 5 or 6 digit number. Your question is well timed. I realized the SUBSTRING expression needed tweaking to account for varied length. It now looks like this -
LEN((DT_STR,6,1252)CNV_HTDTEP) == 6 ? (DT_DBDATE)(SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,1,2) + "/" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,3,2) + "/" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,5,2)) : (DT_DBDATE)("0" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,1,1) + "/" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,2,2) + "/" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,4,2))
However, I am still receiving the error -
The column "CNV_TRANDATE" can't be inserted because the conversion between types DT_DBDATE and DT_WSTR is not supported |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-25 : 12:39:49
|
so what does that 5 or 6 didgit number represents? give some exmaple values along with equivalent dates
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
2fire
Starting Member
9 Posts |
Posted - 2013-04-25 : 12:59:08
|
Date values either in the form of -
MDDYY or MMDDYY
HTDTEP
101911 = 10/19/11
112210 = 11/22/10
20911 = 2/09/11
32112 = 3/21/12
11112 = 1/11/12
111710 = 11/17/10
|
|
|
|
2fire
Starting Member
9 Posts |
Posted - 2013-04-25 : 18:28:12
|
I figured out a soluton to the problem. Here's the new derived column expression -
LEN((DT_STR,6,1252)CNV_HTDTEP) == 6 ? (DT_DBTIMESTAMP)("20" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,5,2) + "-" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,1,2) + "-" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,3,2)) : (DT_DBTIMESTAMP)("20" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,4,2) + "-" + "0" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,1,1) + "-" + SUBSTRING((DT_STR,6,1252)CNV_HTDTEP,2,2))
Then changed the SQL table Data Type to smalldatetime.
Thanks for your asistance!
|
|
|
|
|
|
|
Problems Converting to Date