Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2784
  • Last Modified:

Excel Import Data from SQL- Date comes across as text

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
Mehawitchi
Asked:
Mehawitchi
1 Solution
 
RobOwner (Aidellio)Commented:
Is it stored as a date type on your database? What is the format it's coming back ?
0
 
RobOwner (Aidellio)Commented:
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
 
innosysCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
RobOwner (Aidellio)Commented:
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
 
Robberbaron (robr)Commented:
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
 
MehawitchiAuthor Commented:
Thanks to all contributions
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now