convert string YYYYMM into date SSIS

Question

convert string YYYYMM into date SSIS

joanna.seldon

Ten Centuries

Points: 1188
More actions
May 15, 2017 at 7:26 am

#330346

Hi
new to SSIS ,
I am trying to convert a column "Date" in excel YYYYMM (Currently a string) into a date to go into a SQL server table
Example 201703
I have seen many answers to convert YYYYMMDD but not YYYYMM
I been trying to use a Derived Column but no joy,
please help

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

joanna.seldon - Monday, May 15, 2017 7:26 AM
Hi
new to SSIS ,
I am trying to convert a column "Date" in excel YYYYMM (Currently a string) into a date to go into a SQL server table
Example 201703
I have seen many answers to convert YYYYMMDD but not YYYYMM
I been trying to use a Derived Column but no joy,
please help

How do you determine which day of the month you want to use? First? Last? Random?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

joanna.seldon Ten Centuries Points: 1188 More actions · Answer 2

Hi

that is a good point

I would use the 01 (First) of the month

please help

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

joanna.seldon - Monday, May 15, 2017 7:36 AM
Hi
that is a good point
I would use the 01 (First) of the month please

The typical solution, then, is to concatenate '01' to the end of that string and then do a simple direct conversion to a date/time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

joanna.seldon - Monday, May 15, 2017 7:36 AM
Hi
that is a good point
I would use the 01 (First) of the month
please help

Try something like this
(DT_DATE) ( left(@DodgyDate,4) + "-" + right(@DodgyDate,2) + "-01")

Replacing @DodgyDate with your own variable/column name, of course.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Jeff Moden - Monday, May 15, 2017 7:51 AM
joanna.seldon - Monday, May 15, 2017 7:36 AM
Hi
that is a good point
I would use the 01 (First) of the month please
The typical solution, then, is to concatenate '01' to the end of that string and then do a simple direct conversion to a date/time.

And fail miserably because SSIS won't recognize that format (who decided that?).
You would need to add some delimiters.
(DT_DATE) ( LEFT(@[User::Month],4) + "/" + RIGHT(@[User::Month],2) +"/01")

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Luis Cazares - Monday, May 15, 2017 8:12 AM
Jeff Moden - Monday, May 15, 2017 7:51 AM
joanna.seldon - Monday, May 15, 2017 7:36 AM
Hi
that is a good point
I would use the 01 (First) of the month please
The typical solution, then, is to concatenate '01' to the end of that string and then do a simple direct conversion to a date/time.
And fail miserably because SSIS won't recognize that format (who decided that?).
You would need to add some delimiters.
(DT_DATE) ( LEFT(@[User::Month],4) + "/" + RIGHT(@[User::Month],2) +"/01")

Thanks, Luis. That's a bloody amazing short-coming, IMHO. I keep trying to be open minded about SSIS but things like this drive another nail into the list of reasons I don't care for it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden - Monday, May 15, 2017 9:57 AM
Thanks, Luis. That's a bloody amazing short-coming, IMHO. I keep trying to be open minded about SSIS but things like this drive another nail into the list of reasons I don't care for it.

I feel like a challenge: let's see if we can find something about SSIS that you would like 🙂
Imagine the situation where you have a delimited string. You don't know its length, nor how many elements it may contain. But you do know that you always want to return the fourth item in the string.
That's a simple one-liner in SSIS:
token( @[User::Variable],"/",4)
where @[User::Variable] is the variable containing the delimited string, / is the delimiter and 4 is the occurrence number.
That beats any T-SQL solution I can think of.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Phil Parkin - Monday, May 15, 2017 10:14 AM
Jeff Moden - Monday, May 15, 2017 9:57 AM
Thanks, Luis. That's a bloody amazing short-coming, IMHO. I keep trying to be open minded about SSIS but things like this drive another nail into the list of reasons I don't care for it.
I feel like a challenge: let's see if we can find something about SSIS that you would like 🙂
Imagine the situation where you have a delimited string. You don't know its length, nor how many elements it may contain. But you do know that you always want to return the fourth item in the string.
That's a simple one-liner in SSIS:
token( @[User::Variable],"/",4)
where @[User::Variable] is the variable containing the delimited string, / is the delimiter and 4 is the occurrence number.
That beats any T-SQL solution I can think of.

Now that's useful. How's the performance?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)