Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Problems Converting to Date

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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!
Go to Top of Page
   

- Advertisement -