Importing data from SQL

[RichardMGreen]

Hi all

I'm importing some data from an SQL database into Excel 2010.
Everything is fine except the dates are pulling thorugh as text items rather than "proper" dates.

If I run the query in SQL and copy/paste the data into Excel, then the dates appear correctly.

I've tried setting the relevant columns to date format before I run the import but that hasn't made any difference.

Anyone any ideas on how to get the dates to display correctly from the import?

 

[Joe4]

It sounds to me like maybe the field is of Text (and not Data type) in SQL.
Are you importing a SQL table or a SQL Query?
If a table, can you confirm the Data Type of this field?
If a query, can you post the SQL code for it?

 

[RichardMGreen]

Hi Joe

The fields are datetime format and it's a view rather than a table (not sure if that makes a difference).
The field originally holds date and time but I only need the date part (so i was CASTing it as a date).
I've managed to get round it by casting it as a date and then as datetime (so you end up with midnight as the time).

The view (for one of the fields) has gone from this:-
CAST(EndDateTime AS DATE) AS Ep_End_Date

to this:-
cast(CAST(EndDateTime AS DATE) as datetime) AS Ep_End_Date

There's no visible performance drop, I'm still curious as to why it's happening though.

 

[Joe4]

Not sure.
Are there any Nulls in your data, or other strange entries in your date column?
How is the data being imported into Excel?

I have seen it happen when importing text files into Excel, if they have a weird data format Excel doesn't understand, it sometimes converts it to Text (sometimes the format of the Time piece seems to confuse Excel).

 

[RichardMGreen]

No nulls or blanks in any of the date fields (there are actually 4 of them).
I'm using the data import wizard (such as it is) in Excel 2010 on the Data ribbon and then selecting From Other Sources/From SQL Server.
Now I'm CASTing at as a datetime (instead of just date), I can format the cells as short date and it all seems to work OK.

When I was just CASTing as a date, formatting the cells in Exel as short date made no difference.

 

[Joe4]

Odd. Don't really know, unless it has something to do with Regional Date settings or something...

At least you got it working OK now.

 

[RichardMGreen]

That makes two of us, but at least it's working.
I'll keep looking into it (when I have time) and let you know if I find anything.

 

[Joe4]

I'll keep looking into it (when I have time) and let you know if I find anything.

Please do, I am very curious...

Thanks