Convert date to "YYYYMM" in ssis package

Question

Post reply

Convert date to "YYYYMM" in ssis package

chinni-652949

Default port

Points: 1425
More actions
November 2, 2010 at 10:48 am

#230622

We are pulling data from oracle and loading in to SQL server
One column is report_time which is like datetime and need to be formated like YYYYMM when loading in to Sql .
How to convert date in to this format in SSIS..... Do we need to use data coversion or script task?
Please let me know. Thank you

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Data conversion should be faster

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

chinni-652949 Default port Points: 1425 More actions · Answer 2

Thank you.

But how can I format in Data Conversion to YYYYMM.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

chinni-652949 (11/2/2010)
Thank you.
But how can I format in Data Conversion to YYYYMM.

Take the year and month from the date; convert them to strings; pad them to the right length; then concatenate them.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

chinni-652949 Default port Points: 1425 More actions · Answer 4

chinni-652949

Default port

Points: 1425

November 2, 2010 at 12:34 pm

#1245642

can we do all that in data convertion task

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

chinni-652949 (11/2/2010)
can we do all that in data convertion task

I believe you should be able to. I do not have time to try it, but it should just be a matter of combining a few instructions in the same expression.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

chinni-652949 Default port Points: 1425 More actions · Answer 6

(DT_STR, 6,1252)((DT_STR,4,1252)YEAR(DATE_COLUMN) +

(DT_STR,4,1252)MONTH(DATE_COLUMN))

I used this expression in derived column task ...... date_column in datebase timestamp (source) and destination column is int.

I am getting error in oledb destination....cannnot convert DT_DBtimestamp to DT_14.

tmitchelar Hall of Fame Points: 3391 More actions · Answer 7

Year and month only isn't an acceptable form of a date datatype in SQL Server. You'll have to store a day too (I just default it to the first of the month) and then strip it out at the reporting layer if I need to.

chinni-652949 Default port Points: 1425 More actions · Answer 8

its working.

I created new column in derived column and used the above expression with datatye as string.It worked.