Problems Converting to Date - SQL Server Forums

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

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources