January 18, 2021 at 3:50 am
Dear Group:
I am not sure if this is a T-SQL / SSMS issue, or Excel, so if this is off-topic please accept my appologies. I am hoping there is something I can do inside my SQL that can accomodate for this, but I haven't been able to find a solution via web-searches.
I have a query that pulls a Date / Time column: 2020-12-24 03:30:27.0000000
However, when I copy this value into Excel, it shows like this: 30:27.0
As such, I need to go into Excel and format each column. Is there a way I can write my SQL statement to avoid having to do this? I am not doing anything in my SQL statement except: SELECT DateColumn FROM Table.
Any help is appreciated.
January 18, 2021 at 11:11 am
Taking SQL Server out of the equation for a moment, can you find a data format which Excel interprets the way you want, on pasting?
Once you have that, post it here and someone will provide the T-SQL to format your date that way, ready for pasting. Hopefully that will work out!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 18, 2021 at 12:14 pm
Dear Group:
I am not sure if this is a T-SQL / SSMS issue, or Excel, so if this is off-topic please accept my appologies. I am hoping there is something I can do inside my SQL that can accomodate for this, but I haven't been able to find a solution via web-searches.
I have a query that pulls a Date / Time column: 2020-12-24:03:30:27.0000000
However, when I copy this value into Excel, it shows like this: 30:27.0
As such, I need to go into Excel and format each column. Is there a way I can write my SQL statement to avoid having to do this? I am not doing anything in my SQL statement except: SELECT DateColumn FROM Table.
Any help is appreciated.
I have the same gripe about Excel.
For some reason, unknown to me, Excel adds another space between the date and the time when you paste it in. This stops Excel from formatting it correctly.
January 19, 2021 at 6:12 pm
The format I had been using in Excel is: Format Cells --> Custom --> m/d/yyyy h:mm:ss
Excel technically only lists the custom format as: m/d/yyyy h:mm and I add the extra ":ss" to get it how I need.
January 19, 2021 at 6:17 pm
The format I had been using in Excel is: Format Cells --> Custom --> m/d/yyyy h:mm:ss
Excel technically only lists the custom format as: m/d/yyyy h:mm and I add the extra ":ss" to get it how I need.
My question was not about Excel custom date formats. I asked whether it was possible to format the data coming from SQL Server (ie, the date string) in such a way that Excel interprets it the way you want when it is pasted?
I have no idea whether this is possible, but if you find a suitable format, someone here will be able to provide the T-SQL which will present your dates in that format.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 19, 2021 at 7:51 pm
If you leave off the sub-seconds and do not include a period - Excel should display it correctly. This appears to work just fine: convert(char(19), getdate(), 121).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 20, 2021 at 2:33 pm
I know I'm not answering your question here.
I'd be mindfull about copying and pasting data from SQL to Excel because of locales; how does Excel know what format your date is in e.g. is 1/2/2021 the 1/feb or 2/Jan. I suppose you could get TSQL to format as 1 jan 2021 for the Eng speaking world.
In my experience Excel doesn't like YYYYMMDD
To avoid this I get Excel to execute the Query via ODBC rather than pasting. This way formats are ok. Quickly found this via Google
https://www.lifewire.com/excel-front-end-to-sql-server-2495398
Probably better examples on the web.
I normally find I have to connect via the Excel wizard to a SQL table just to create the connection- small table so its quick.
Once this is done I edit Excel's connection properties and replace with TSQL or a Stored proc call e.g.
EXEC sp_myStoredProc @p='Customer'
I save the workbook. Anyone using this workbook can format as they desire/move columns around and it keeps the format between data refreshes.
I believe it does this by sticking data into an Excel table on the sheet and you can use Excel table references to get to cells via their column names. Although I don't use Excel that much.
January 20, 2021 at 3:54 pm
I know I'm not answering your question here.
I'd be mindfull about copying and pasting data from SQL to Excel because of locales; how does Excel know what format your date is in e.g. is 1/2/2021 the 1/feb or 2/Jan. I suppose you could get TSQL to format as 1 jan 2021 for the Eng speaking world.
In my experience Excel doesn't like YYYYMMDD
To avoid this I get Excel to execute the Query via ODBC rather than pasting. This way formats are ok. Quickly found this via Google
https://www.lifewire.com/excel-front-end-to-sql-server-2495398
Probably better examples on the web.
I normally find I have to connect via the Excel wizard to a SQL table just to create the connection- small table so its quick.
Once this is done I edit Excel's connection properties and replace with TSQL or a Stored proc call e.g.
EXEC sp_myStoredProc @p='Customer'I save the workbook. Anyone using this workbook can format as they desire/move columns around and it keeps the format between data refreshes.
I believe it does this by sticking data into an Excel table on the sheet and you can use Excel table references to get to cells via their column names. Although I don't use Excel that much.
This seems like a lot of work for a quick copy/paste from a query to send someone data in an Excel spreadsheet. Lots of times - these kinds of things are one-off processes that are not meant to be a final solution.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 20, 2021 at 7:07 pm
For a bunch of string fields, numbers you're absolutely right.
I use the last excel file, which I just go into and change the query save it and send it. The real bonus is it saves the query that generated the data. So when people come back to me sometime later with a question on the sheet, I have the exact query that produced it by just looking at connection attributes. Again if the query takes a long time to run this method is a pain.
I have seen Excel on rare occasions screw up copy and paste from results grid CRLFs I think.
January 20, 2021 at 7:25 pm
For a bunch of string fields, numbers you're absolutely right.
I use the last excel file, which I just go into and change the query save it and send it. The real bonus is it saves the query that generated the data. So when people come back to me sometime later with a question on the sheet, I have the exact query that produced it by just looking at connection attributes. Again if the query takes a long time to run this method is a pain.
I have seen Excel on rare occasions screw up copy and paste from results grid CRLFs I think.
Yeah - copy/paste has issues...agreed. But - I would much rather spend my time on generating the query and making it correct than spending time on building a 'solution' in Excel. In almost every case I have worked on - the final solution is going to be a report (SSRS) or a data extraction (SSIS). All we need at that point is validation from the requestor that the data is correct and includes everything needed...before spending time on building the report or extraction.
Further to that - I just do not want to give access to the database for users who have no business (or reason) accessing SQL Server directly. Any Excel based solution would require users to have direct access and permissions - which becomes a management issue (either individual SQL accounts, domain accounts - or domain security groups, etc...).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 20, 2021 at 8:01 pm
Copying SQL into Excel's connection properties window and clicking refresh.
The data is saved in the workbook. If the user has SQL rights an optional refresh will work otherwise fail but they can see the data when the workbook was saved.
January 20, 2021 at 8:06 pm
There is a thread on stackoverflow about this with various different solutions:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply