SSMS & Excel - Copy Paste - DateTime Columns

  • 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.

  • 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!

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • AMCiti wrote:

    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.

  • 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.

  • AMCiti wrote:

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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.

     

     

  • terry999 wrote:

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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.

  • terry999 wrote:

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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.

     

  • There is a thread on stackoverflow about this with various different solutions:

    https://stackoverflow.com/questions/18598075/datetime-fields-from-sql-server-display-incorrectly-in-excel

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply