Datetime (Date) Data to Excel Become String Type — How to change Back as Datetime


When your export datetime data from SQL Server to excel the result excel column become text type. You cannot format it as either date/time format or custome format within excel directly.
There are a few solutions to solve this problem.
You can simply to use TXET function to format the text directly.
Use the TEXT function in cell as formula along with the formated datetime style.
For example: =TEXT(B2,”mm/dd/yyyy”) or =TEXT(A2,”yyyy-mm-dd”) (you need to copy and paste this formula the entire column ). You can choose any other valid datetime style to format the data in the column But this is not optimum since the result is still text.

There are other functions in Excel to we can use to retrieve datevalue and timevalue from the string datetime.

=DATEVALUE(LEFT(A1,10))
–yyyy-mm-dd “2017-01-16” If this value is shwing an integer (which is a date value in integer form),
you need to use built-in format function to format date to the style you need.

=TIMEVALUE(MID(A1,12,8))
–hh:mm:ss

A simply form without using these functions is to use a + sing to add date and time together to force the concatenation and implict conversion to real datetime value.

=LEFT(A1,10)+MID(A1,12,8)

Apply this formula to this column, the datetime now is real datetime in excel and you can use any format within Excel now.
If the data column is DATE string, we can simply use formula: =DATEVALUE(A1).

Another option is to use Data>>Text to Columns >>(delimited default tab)>>chosse Date — pick format YMD. After this conversion, you can format this column (datetime value) to the style you need.

https://jingyangli.wordpress.com/2016/08/03/format-date-column-in-excel-from-sql-server-export-file/

If your query result is not large, you can copy and past the result with header directly to Excel spreadsheet and format the datetime column afterwards.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s