Cast/Conv Expression remove the slash : 02/02/2008

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 2005 Forums

SSIS and Import/Export (2005)

Cast/Conv Expression remove the slash : 02/02/2008

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-27 : 09:25:12

Hi, I need help please.

I have text source & i need to write it to table field being an integer.
I complains about the column, so i added a derived column but I am clueless in the expression:

Source from text: 02/02/2008
Derived column expression to output: 20080202 in a int datatype

I need the expression to do it.

Please Assist!

Regards

X002548
Not Just a Number

15586 Posts

Posted - 2008-03-27 : 09:35:18

I'd say it'd be silly to convert a datetime to an int

Why do you want to do this?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-27 : 09:47:45

Thanks for the assistance.
Receiving the file from an external company in a date format
and out database are running on integers.

The table, date field was already setup (int) so i can't change it.

Please assist.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-03-27 : 10:51:09

I guess you're going to have to dissect and rebuild the whole string.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-27 : 11:13:27

[code]DECLARE @vc VARCHAR(20)

SET @vc = '02/15/2008'

SELECT CAST(SUBSTRING(@vc, 7, 4) + SUBSTRING(@vc, 1, 2) + SUBSTRING(@vc, 4, 2) AS INT) AS int1,
CAST(CONVERT(CHAR(8), CONVERT(DATETIME, @vc, 101), 112) AS INT) AS int2[/code]

E 12°55'05.25"
N 56°04'39.16"

tm
Posting Yak Master

160 Posts

Posted - 2008-03-27 : 14:57:28

If you are using the SSIS Derived Column Data Flow you can try this expression in the Derived Column Transformation Editor ..

(DT_UI4)((DT_STR,4,1252)YEAR(dt) + (DT_STR,4,1252)MONTH(dt) + (DT_STR,4,1252)DAY(dt))

Set your data type to four-byte unsigned integer

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-28 : 03:04:28

Hi, Thank You all.
The expression for the drived column i'm struggling with:

(DT_UI4)((DT_STR,4,1252)YEAR([Trx_Date]) + (DT_STR,4,1252)MONTH( [Trx_Date]) + (DT_STR,4,1252)DAY( [Trx_Date]))

Data type: four byte unsigned

Error: The function Year does not support the data type DT_UI4 for parameter 1. Has errors such as divide by zeros.

Please Assist

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 03:09:58

quote:
Originally posted by ismailc

(DT_UI4)((DT_STR,4,1252)YEAR([Trx_Date]) + (DT_STR,4,1252)MONTH( [Trx_Date]) + (DT_STR,4,1252)DAY( [Trx_Date]))

Month and day are maximum 2 characters long.

(DT_UI4)((DT_STR,4,1252)YEAR([Trx_Date]) + (DT_STR,2,1252)MONTH([Trx_Date]) + (DT_STR,2,1252)DAY([Trx_Date]))

E 12°55'05.25"
N 56°04'39.16"

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-28 : 03:36:07

Thank You, but i get the same error:

(DT_UI4)((DT_STR,4,1252)YEAR([Trx_Date]) + (DT_STR,2,1252)MONTH([Trx_Date]) + (DT_STR,2,1252)DAY([Trx_Date]))

tm
Posting Yak Master

160 Posts

Posted - 2008-03-28 : 10:54:05

Can you give a couple of lines of data from your text file.

Also when you import the text file does the field import as a datatime field?

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-31 : 02:20:23

Hi,
data:
274,SM MALBOROUGH,2008.02,04/02/2008,006437,STERI STUMPIE 6x350ml
274,SM MALBOROUGH,2008.02,04/02/2008,006438,STERI STUMPIE BANANA
274,SM MALBOROUGH,2008.02,04/02/2008,006440,STERI STUMPIE ST/BERRY
274,SM MALBOROUGH,2008.02,04/02/2008,006441,STERI STUMPIE CHOC

All External columns are: string [DT_STR]
Output: string [DT_STR]

Please Assist, I have to get this going!

Regards

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-31 : 06:25:43

Hi, got it going!

My expression did not work becuase the year/month/day only works if the datatype is datetime.

So i done a DataConversion to: database timestamp
and then derived column: (DT_STR,4,1252)YEAR([Copy of Trx_Date]) + RIGHT("00" + (DT_STR,2,1252)MONTH([Copy of Trx_Date]),2) + RIGHT("00" + (DT_STR,2,1252)DAY([Copy of Trx_Date]),2)

Thank You ALL

Regards

vl
Starting Member

14 Posts

Posted - 2008-03-31 : 16:12:29

I am very new on SSIS:

What control you would use when you load a flat file to a table and need to twist the field before load it into the table.

the expression would similar like this:

EmployeeNumber = Trim(column(0))
EmployeeNumber = Left(EmployeeNumber, Len(EmployeeNumber) - 1)

tm
Posting Yak Master

160 Posts

Posted - 2008-04-01 : 09:56:47

vl .. You can use Derived Column data flow.

Basically create a derived column from EmployeeNumber.

In the Derived Column Expression you would need something like ..

SUBSTRING(EmployeeNumber, 1, LEN(TRIM(EmployeeNumber)) - 1)

The above expression have not been tested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources