Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel Import Data from SQL- Date comes across as text

Posted on 2013-12-31
6
2,323 Views
Last Modified: 2014-01-02
Hello experts,

I'm importing data from an MS SQL database into an Excel sheet using data connection. The SQL table includes a Date column, but this column comes to Excel as text rather than date.

Is there any way I can fix this issue?

The good news is..I can loop through each and every cell in that particular column using VBA code, so perhaps this could help coming up with a solution.

Thank you,
Hani
0
Comment
Question by:Mehawitchi
6 Comments
 
LVL 43

Expert Comment

by:Rob
ID: 39749403
Is it stored as a date type on your database? What is the format it's coming back ?
0
 
LVL 43

Expert Comment

by:Rob
ID: 39749558
I'm suspecting it's something like "yyyy-mm-dd"? In which case you'll need to parse the date as a string in your sql to the following format "mm/dd/yyyy" That should be enough for excel to recognize it as a date
0
 
LVL 1

Expert Comment

by:innosys
ID: 39750346
you can separate the string into 3 columns and rebuild the date depending on the date format
like mid(e1,0,2) as month and mid(e2,4,2) as day and mid(e2,7,4) as year and then rebuild the date in excel sheet

it will help and it isvery good to solve this issue
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 43

Accepted Solution

by:
Rob earned 500 total points
ID: 39750365
The easiest is to format the date to how excel likes it using the SQL Convert function in your SQL.

Eg

Select *, CONVERT(VARCHAR(10), my_date_column, 103) as newdate FROM mytable;

The 103 indicates that it will be formatted as dd/mm/yyyy
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39750821
as rob jurd suggested, please advise the column format in SQL server table.

But for me, I ALWAYS format dates as yyyy-mmm-dd  as it is unambiguous.

For me in Oz, the std display format is dd/mm/yyyy, but that doesn't import correctly.  So if i use dd/mmm/yyyy, the display is ok, and all conversions and imports work fine too.
0
 

Author Comment

by:Mehawitchi
ID: 39750839
Thanks to all contributions
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question