FreeTDS Development Group

["James K. Lowden" <jklowden AT speakeasy.org>]

Hi Bradley, 

Bradley Bell wrote:
> isql uses db-lib, so maybe this is a limitation of db-lib?

What you're seeing in isql is db-lib's default conversion, the result (I
assume) of calling dbbind to make a string of a datetime field.  A
db-lib application can call dbdatecrack() and construct any string it
likes (or rely on server-side conversions, of course).  

You might be candidate #2 for Eric Deutsch's suggestion (Fri, 7 Dec 2001
16:22:30 -0800 (PST)) that freetds.conf allow a configurable default
datetime-to-varchar conversion.  

Eric Deutsch wrote:
> It would be sensational if one could specify in freetds.conf one's own
> strftime format string.  I like all my dates to show up as
> '2001-12-07 15:45:22' without having to CAST and CONVERT and stuff.  I do
> this by manually going into the FreeTDS driver and replacing the default
> format string with the one that produces this.

For the time being, you might want to apply his patch.  I'd be tempted
instead to interpose views or stored procedures between the tables and
the application to deal with vendor-specific behavior.  

> > > (I don't want to use CONVERT, because the select statements in this
> > > application should remain portable)
> >
> > If the server is consistent (always MS SQL Server or Sybase), the most
> > portable way probably *is* to coerce the format with convert.
> 
> It needs to be portable to several different backends.  MS/Sybase is the
> only one that seems to need a custom select statement for timestamp/datetime
> values.  I see that when freetds is being used as an odbc driver, it also
> returns:  (with unixodbc's isql)
> SQL> select getdate();
> +--------------------+
> | Dec 17 2001 12:28PM|
> +--------------------+
> That's not right, is it?
> 

Depending on what "right" is.  I think a bug is "behavior at variance
with the documentation"; by that definition FWIW, I'd say what you're
seeing is right.  

There's no universally accepted standard describing how database drivers
are supposed to convert datetime information to strings.  Microsoft says
it's up to the driver; they make two drivers with different behavior. 
FreeTDS makes three drivers with one behavior (and, uh, none
documented).  Who's to say what's "right"?  

Because of Eric's suggestion, I've been thinking about this problem
quite a bit on and off lately.  I don't know where the Principle of
Least Astonishment leads us.  Mimic Sybase's behavior (cf. Michael
Peppler's observations on CS_LOCALE and ct-lib)?  Mimic Microsoft's
idiosyncratic choice?  Have a setting configurable at runtime, or
compile time, settable per installation, per server, per database, per
API, or per connection?  

So far, the two people who'd like to have ANSI date formats by default
want them all the time, for all hosts regardless of what library they're
using.  I think a single global setting is bound to surprise some future
webmasters sitting atop PHP scripts sooner or later.  I'm guessing that
the right level of granularity is:

1.      one setting in freetds.conf for each API set: db-lib, ct-lib, and
ODBC.  

2.      A faithful emulation of Sybase's choice would further suggest we
follow their locale strategy for ct-lib (while allowing a freetds.conf
override).  

It so happens I do have a half-completed module for datetime conversions
on the bench, which I'll have to merge with the recently-completed bcp
enhancements when the time comes.  But I have kids and Christmas and
skiing on the near-term agenda, so please don't hope for anything before
February, even assuming Brian likes the result.  

That's where I'm up to.  What do you think?

--jkl