split column into column

10 pts.
Tags:
Microsoft Excel
SQL Server 2005
SSIS
I need to Split the single datetime column into separate date and time column in SSIS package and exprot the data into Excel Sheet. Can any one guide how to do it ? Thanks in advance.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Alternatively, if you want to separate date and time using T-SQL, you can use CONVERT function as follows :

Syntax
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Example :
CONVERT (VARCHAR(10), dbo.EMPLOYEES.BIRTH_DATE, 103)
(Style Code 103 returns date in dd/mm/yyyy format

You can see all Style Codes in SQL Server Books Online (Help on Transact-SQL)

Fanis Bellos
Technical Manager
INFOPOWER LTD

—————————————————————————————————————-

Is it required to be split before going to Excel? It is very easy to split columns once the data is in Excel using the Data, TextToColumns feature.

The Text to Column command allows you to break text in one column into several columns. For example, if you have LastName, FirstName in a column, you can break the text into two columns—one containing LastName and the other containing FirstName.

1. Insert as many columns to the left of the column to be split as needed.
2. Select the cells to be separated.
3. Pull down the Data menu and choose Text to Columns
4. Choose Delimited and click on Next
5. Choose the Delimiter that is used in the column, such as comma and click on Next
6. To set the column data format, click on the column and then click on the format desired under column data format (this is optional). Repeat this for each column and click on Finish.

I frequently use the datevalue() and timevalue() function in Excel to just get either the date or the time from a text field.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following