+ Reply to Thread
Results 1 to 3 of 3

ODBC date field displays as 1/0/1900

  1. #1
    Registered User
    Join Date
    04-27-2010
    Location
    Louisville KY USA
    MS-Off Ver
    Excel 2003
    Posts
    25

    Angry ODBC date field displays as 1/0/1900

    I can't get a SQL Server date field to format as a date. I have tried short date, long date, custom formatting including hours/minutes/seconds, and everything is interpreted as 0. If I use an existing connection and put it into a table report all dates display as expected. If I put them in a pivot (using direct ODBC, not a range in another sheet) I get the 1/0/1900 again. The query is basically:

    SELECT MAX(PayDate) FROM MyTable

    I have even tried a simple query like this with the same results.

    SELECT 'ABC' AS Category, CAST('03-25-2014' AS DATE) AS MyDate

    I have seen other posts relating to VLOOKUPs and similar issues but none of those responses seem to fit what I'm seeing.

    The pivot just does a max aggregation of a date. This makes is seem like MAX of a date is perfectly "legal".

    http://www.techrepublic.com/blog/mic...bset-in-excel/

    Any ideas?

    Thanks.

  2. #2
    Registered User
    Join Date
    11-13-2006
    Posts
    87

    Re: ODBC date field displays as 1/0/1900

    I don't know SQL server, but it might be a serial date stored as a sting. "41723.8814360"

    If This is the case then

    Use Variable*1 to convert it to a number (double.)

    All Windows dates and times are stored as Doubles, Office Apps just display them as formatted strings and converts dates entered as string to doubles.
    Try this experiment: In Excel, Format Column A as General, Column B As Number with 4 decimals, and Column C as a Date that shows the month, day, and year.
    In A1 enter 1/30, in A2, 1/50
    In B1, "=A1", in C1 "=B1", in B2 "=A2", in C2 "= B2"

    To store Windows Dates on SQL server as a formatted string, format them first;Var = Format(Date, "mm/dd/yyy"). Put Var on the Server. MS Office will use that string as a serial date/time (double.)

    End If

  3. #3
    Registered User
    Join Date
    04-27-2010
    Location
    Louisville KY USA
    MS-Off Ver
    Excel 2003
    Posts
    25

    Re: ODBC date field displays as 1/0/1900

    BAH!!! I hate Excel.

    So here's the deal. My data type for the SQL Server column is DATE. So Excel doesn't like this:

    SELECT MyDateField FROM MyTable

    nor does it like:

    SELECT CAST(MyDateField AS DATE)

    HOWEVER, for whatever unacceptable reason, it DOES like this!

    SELECT CAST(MyDateFieldThatIsAlreadyADateDataType AS DATETIME)

    DATETIME data type is the difference. REALLY stupid, REALLY annoying, shouldn't be that way, but thanks to Microsoft I have spent hours of my time finding a stupid bug (a bug in my opinion, anyway).

    So, there's your answer. Thanks for your help SamT!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro for changing the date from 02-Jan-1900 to 01-Jan-1900
    By Lavanya Anandan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2013, 07:08 AM
  2. [SOLVED] How to set-up a field that displays the last updated date
    By teh_chucksta in forum Excel General
    Replies: 1
    Last Post: 07-14-2006, 08:05 PM
  3. how do i TRUNC a date field using SQL Server ODBC ?
    By Nick Newton in forum Excel General
    Replies: 0
    Last Post: 06-22-2006, 09:20 AM
  4. format a cell to use mo/date with no year:,displays 1900?
    By SKI CLUB BILL in forum Excel General
    Replies: 2
    Last Post: 04-20-2006, 12:00 AM
  5. [SOLVED] Excel's ODBC driver's field limit
    By Jo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-11-2005, 01:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1