FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: Winkless, Geoff [mailto:geoff.winkless AT pera.com]
> Sent: December 17, 2001 4:34 AM
> Subject: [freetds] Re: datetime format
>
> James K. Lowden wrote:
> > MS SQL Query Analyzer is an ODBC tool.  It's default datetime 
> > display is controlled by some ODBC settings.  I don't know 
> > the details.  
> 
> No it's not. It's specifically for MS SQL Server - basically it's the
> updated version of isql/w. You're probably thinking of MS 
> Query, the crappy
> thing that comes with Office.

Geoff, 

Perhaps I should have said SQL Query Analyzer is an "ODBC-based" tool.  (I
also wish I'd gotten my "It's" right.  Oh, well.)  In Books Online, under
"Connecting to a SQL Server Data Source", you'll find this:

"SQL Server Query Analyzer is an example of an ODBC application that does
not use a data source; Microsoft Excel is an example of an ODBC application
that does use a data source."

Interesting notion: a data tool that works without a data source.  ;)

Driver differences are what Bradley bumped into.  From the docs:

"Another set of rules applies when converting between Transact-SQL objects
such as parameters, return codes, and result set columns and their bound
program variables. [...]  The rules can vary among between the APIs. For
example, the SQL Server ODBC driver supports converting the data from a
datetime result set column into an ODBC timestamp data structure, and the
DB-Library interface does not allow this conversion because it does not
support ODBC timestamp data structures."

Some "for example", eh?  :/ The behavior is easily illustrated.  

>From SQL Query Analyzer:

select getdate()
                           
--------------------------- 
2001-12-17 12:27:34.910

>From isql:

1> select getdate()
2> go

 --------------------
  Dec 17 2001 12:28PM

>From osql:

1> select getdate()
2> go

 -----------------------
 2001-12-17 12:28:31.673

<rant>I have lots of complaints about the opacity of these tools.  For one
thing, discovering the datetime conversion behavior of ODBC requires -- like
everything else in ODBC -- inference and experimentation, not to mention a
willingness to deal with loose usage of words like "standard".  And me, I'm
an American in an English-only environment.  What it must be like for anyone
whose environment is even slightly more heterogeneous gives me heartburn
even to contemplate.  </rant>

But it all boils down to something very unsurprising: different drivers
exhibit different behavior.  

Regards, 

--jkl