datetime format

Brian Bruns camber at ais.org
Tue Dec 18 19:55:45 EST 2001



I guess I'll weigh in here.  I believe we chose the current date format to 
avoid problems with internationalization.  We probably should support some 
other formats as well in some form.  I have no real desire to introduce 
another file ala locales.dat.  The freetds.conf section should be flexible 
enough to handle this.  Perhaps a [locales] section? Or should time be 
stipulated per server?

Brian 

On Tue, 18 Dec 2001, James K. Lowden wrote:

> 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
> 
> ---
> You are currently subscribed to freetds as: [camber at ais.org]
> To unsubscribe, forward this message to $subst('Email.Unsub')
> 




More information about the FreeTDS mailing list