Pasting Dates - Why do they become formatted as Time (1 Viewer)

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 06:31
Joined
Jan 20, 2009
Messages
12,542
When pasting data from SQL Server query results into Excel, dates always become formatted as Time even when the column has already been explicitly formatted as Date.

Office applications can make some really dumb decisions trying to second guess what is required but this one is ridiculous. The pasted dates don't even have a Time component so they all display as 00:00:00. So why override the column's predefined Date format to Time? Meanwhile values that are quite obviously numbers will be presented as dates if pasted into a preformatted Date column. Why not override them?

Date columns in the SQL results can be formatted to just be just a date but then Excel decides it is text despite looking exactly like dates.

Anyone know how to avoid this nonsense?
 

Ranman256

Well-known member
Local time
Today, 16:31
Joined
Apr 9, 2015
Messages
4,126
Normally my sql svr date fields transfer as dates (and they ARE date type), but SOMETIMES the 'date' field shows as 'm/dd/yyyy 00:00:00' .
Access says this is NOT a date and wont format. (ticks me off since it's a date like the others)

So I've had to parse it out as a string, and put it in a date variable or field.
 

Minty

AWF VIP
Local time
Today, 21:31
Joined
Jul 26, 2013
Messages
9,391
I don't know the absolute answer to your question, but I think it is to down to the time portion having a greater accuracy (the number of decimals places in the time part)
If you format the output in SQL to remove the milliseconds it will display correctly.

If you don't need the milliseconds apparently SMALLDATETIME works;
SELECT GETDATE(), CAST(GETDATE() AS SMALLDATETIME)

Quite a lot of words about it here https://stackoverflow.com/questions...-from-sql-server-display-incorrectly-in-excel
And yes it really is bloody annoying :rolleyes:
 

Users who are viewing this thread

Top Bottom