Re: [GENERAL] date comparisons

OK, I researched this and have developed the attached patch, which I
have applied to CVS HEAD.

The problem with our code is that when using to_timestamp() or to_date()
without "TM", we assume a fixed maximum length, even if the input
string is variable length, like month or day names. Oracle assumes "TM"
(trim) for such input fields, and this patch does the same.

I think it is too risky to backpatch to 8.2.X.

If anyone sees anymore cases of this in the code, please let me know.

---------------------------------------------------------------------------

Tom Lane wrote:
> "Belinda M. Giardine" <giardine(at)bio(dot)cse(dot)psu(dot)edu> writes:
> > Should it be this way?
>
> Well, to_timestamp() is apparently designed not to complain when the
> input doesn't match the format, which is not my idea of good behavior
> ... but your example is in fact wrong. 'Month' means a 9-character
> field, so you are short a couple of spaces.
>
> regression=# select to_timestamp('January 2006', 'Month YYYY');
> to_timestamp
> ------------------------
> 0006-01-01 00:00:00-05
> (1 row)
>
> regression=# select to_timestamp('January 2006', 'Month YYYY');
> to_timestamp
> ------------------------
> 2006-01-01 00:00:00-05
> (1 row)
>
> You probably want
>
> regression=# select to_timestamp('January 2006', 'FMMonth YYYY');
> to_timestamp
> ------------------------
> 2006-01-01 00:00:00-05
> (1 row)
>
> Or, as suggested upthread, forget to_timestamp and just use the native
> timestamp or date input conversion, which on the whole is a lot more
> robust (it *will* throw an error if it can't make sense of the input,
> unlike to_timestamp).
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +