Avatar of Mehawitchi
Mehawitchi asked on

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
Microsoft ExcelVisual Basic.NET

Avatar of undefined
Last Comment
Mehawitchi

8/22/2022 - Mon
Rob

Is it stored as a date type on your database? What is the format it's coming back ?
Rob

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
AlMounkez Consultant

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Rob

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Robberbaron (robr)

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.
ASKER
Mehawitchi

Thanks to all contributions