How to store date value'30/04/2012' to YYYYMM in sql table by derived column of SSIS?

Question

Post reply

How to store date value'30/04/2012' to YYYYMM in sql table by derived column of SSIS?

KumarVelayutham

SSCrazy

Points: 2204
More actions
September 5, 2012 at 4:38 pm

#266338

Hi ,
I am going to store the date value data'30\04\2012' coming from csv into sql table column like'201204'.
Please help me how to set the expression of SSIS derived column ?
Regards,
Kumar

Viewing 11 posts - 1 through 10 (of 10 total)

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

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 1

KumarVelayutham

SSCrazy

Points: 2204

September 6, 2012 at 2:39 am

#1534305

Any help??????????????

Regards,
Kumar

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Use the SUBSTRING() function.

Have a try and post back with how you get on.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 3

I need data like 201204 and storing into int datatype.Substring will give \ also???????????

Regards,
Kumar

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 4

i have unable to set this expression in derived column.Pls help me..

select convert(varchar(4),month(getdate()),105) + convert(varchar(4),year(getdate()))

Regards,
Kumar

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

Derived columns in SSIS do not use T-SQL syntax - they use SSIS Expression syntax, which is a bit like C#. So no selects, converts etc. You just need to know the column name and start positions/lengths.

Have a look at some more examples on-line & you'll see what I mean.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 6

I'm a bit unclear of your requirements but I assume you want to extract the Month and Year from a given date and create a string from the components in the format YYYYMM.

If so try this:

SELECT CAST(YEAR(GETDATE()) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(GETDATE()) AS NVARCHAR(2)),2)

Good luck!

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

Phil Parkin SSC Guru Points: 246985 More actions · Answer 7

Robin Sasson (9/6/2012)
I'm a bit unclear of your requirements but I assume you want to extract the Month and Year from a given date and create a string from the components in the format YYYYMM.
If so try this:
SELECT CAST(YEAR(GETDATE()) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(GETDATE()) AS NVARCHAR(2)),2)
Good luck!

No - this is not how SSIS derived columns work. The formula for a derived column can be as simple as

SUBSTRING( «character_expression», «start», «length» )

where character expression is typically a column name (in square brackets.)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 8

Thanks Phil, I saw your reply just after I posted mine...

Doing SSIS research as we type...

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

kyliedude SSC-Addicted Points: 492 More actions · Answer 9

Why not consider storing as a DATE data type and then convert to the said format when required. Don't understand why you would want to store as int? What's the reason?

mthurber Mr or Mrs. 500 Points: 523 More actions · Answer 10

My date column is called periodbegin. The derived column is like this:

YEAR(PeriodBegin) * 100 + MONTH(PeriodBegin)