Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2017
    Posts
    1,117

    ODBC query returns wring date format

    Past year our old SQL Server (SQL Server 2005) was put on new hardware and was upgraded (SQL Server 2016). After that I have problems with dates when I use ODBC queries to get data from SQL Databases into Excel (yes, it isn't Access, but I think it is more SQL Server problem).

    I (and other people here too) use "dd.mm.yyyy" as short date format (determined by Windows Regional settings). ODBC query returns dates in format "yyyy-mm-dd" (US Format, I think), which my Excel recognizes as string, not as date. Obviously in upgraded SQL Server some setting is off compared with old server, as in past same queries returned dates in our format. Can someone advice me, where to look for?



    Thanks in advance!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,265
    I use access and normally connecting the table shows a date type, but SOME tables the sql date field connects as string. Even tho all other tables comply as date this one doesn't obey.
    So I must use a query to pull the data by converting it via:

    tMyData is now a query, qsMyData with
    (for us dates)

    cvDate(right([field],2) & "/" & mid([field],6,2) & "/" & left([field],4)) as MyDateFld

    you may be able to use a stored procedure to do this for excel.

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,255
    format "yyyy-mm-dd" is the default for sql server, certainly for later versions.

    What version of excel are you using - 2016? - in 2010 the date converts correctly to regional format if typed in as 2020-03-12

    What value are you seeing in excel if you look in the cell contents?

    2020-03-12 or '2020-03-12

    if the former, then I would suggest the issue is with Excel


  4. #4
    Join Date
    Apr 2017
    Posts
    1,117
    I myself use Excel 2013, other people Excel 2016 (for some reason I was not able to register Excel 2016 in my computer), all Excels are 32-bit, SQL Server is 64-bit.

    This goes weirder and weirder! As those queries are meant for Excel reports, I never did really make any calculations with those dates, I only tried to format them. Now I see, that I can make all calculations, but when I change the format, and then refresh the query, the format I did set doesn't apply.

    When I format cells in query range e.g. to text, and then refresh the query, the new format is applied . I often use this when query has columns like article numbers which often may be numeric strings with leading zeroes.

    When I format any cell/range with date format different from US format, and then refresh the query, the new format is not applied. And when I activate any of such cells in edit mode, and then press Enter, the format changes, but after I refresh the query, all date cells are displayed in US format again. And when I am checking the format of cell, then in Format Cells popup form, the format I did set is displayed instead US format (the format in which I see dates in table).

  5. #5
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    649
    Try using the T-SQL function FORMAT in the ODBC query https://docs.microsoft.com/en-us/sql...l-server-ver15
    It returns a nvarchar in SQL but works (most times) very well when exporting to excel.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-14-2018, 08:59 PM
  2. Format Date query
    By fluffyvampirekitten in forum Queries
    Replies: 4
    Last Post: 10-28-2015, 12:48 AM
  3. Replies: 3
    Last Post: 03-28-2014, 01:02 PM
  4. Replies: 3
    Last Post: 03-28-2014, 07:27 AM
  5. Date Range Query Only Returns Month and Day
    By hammer187 in forum Queries
    Replies: 5
    Last Post: 09-18-2012, 11:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums