> Regards,
> Andrew Eddie
> http://learn.theartofjoomla.com - training videos for Joomla developers

> On 13 April 2012 11:06, Will Daniels <m...@willdaniels.co.uk> wrote:
> > Hi,

> > [I mentioned this a couple of weeks back on the general list and was
> asked
> > to raise it here for platform]

> > Would anybody oppose the addition of a quoteDateTime method to the
> database
> > driver class?

> > The issue is that not all RDBMS use string literals for date/time values.
> > The way things are done currently using getDateFormat (and JDate::toSql
> > accordingly) does not quote the result as a string literal, which means
> > everything that calls it will blindly quote the result afterwards. So
> this
> > makes it impossible to use any other escape notation. For example, ODBC
> > likes timestamp values supplied using the "brace escape" syntax:

> >  { ts 'YYYY-MM-DD hh:mm:ss' }
> >  { d 'YYYY-MM-DD' }
> >  { t 'hh:mm:ss' }

> > The SQL standard specifies a type prefix TIMESTAMP, DATE or TIME, e.g.

> >  TIMESTAMP 'YYYY-MM-DD hh:mm:ss'

> > Microsoft Access (!?) would want:

> >  #YYYY-MM-DD hh:mm:ss#

> > There may be other/better examples, but I think ODBC is the main one and
> is
> > where I came up against this issue.

> > Currently, if I were to override getDateTime to:

> >  return '{ \\t\\s \'d.m.Y H:i:s\' }';

> > ...the correct syntax is produced by PHP in DateTime::format, but
> everywhere
> > in Joomla! this just gets escaped and quoted as a string literal, and so
> it
> > ends up in queries as something like:

> >  AND (foo OR a.publish_up <= '{ts \'24-03-2012 05:43:47\'}')

> > ...instead of the required...

> >  AND (foo OR a.publish_up <= {ts '24-03-2012 05:43:47'})

> > My only solution to this at present has been to resort to rewriting
> queries
> > using regex in the driver class, which is obviously far from ideal.

> > While I understand that all _mainstream_ RDBMS are quite happy to take a
> > string literal for timestamp values, it makes enough sense (to me) to
> add a
> > quoteDateTime in line with quoteName and quote, since this is more
> > convenient for consumers anyway to have the literal string-quoted ready
> for
> > them wherever it does need to be quoted.

> > My proposal is to add this new method alongside getDateFormat and
> > getNullDate to avoid breakage and to patch the Platform and CMS at least
> to
> > use the new method (passing PHP null into quoteDateTime instead of using
> > getNullDate). However, I'm unsure about the possibility of deprecating
> the
> > existing methods because there may yet be some use for an unquoted
> format in
> > DDL statements, since those tend to use a string literal format for
> > specifying DATETIME column DEFAULT values, because DDL is processed
> > differently by the ODBC drivers than SQL.

> > Any thoughts? Objections? If I prepare a commit for this, would it
> likely be
> > accepted? And if so, what do I put in the @since tag, "TBD" or just the
> next
> > version serial?

> > Thanks,
> > -Will