On 4 March 2010 04:08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Brendan Jurd <direvus@gmail.com> writes:
>> For example, you're trying to import a date that is written as "Wed
>> 3rd March, Q1 2010". You might give to_date a format string like 'Dy
>> FMDDTH Month, "Q"Q YYYY' and expect to get the correct answer. If we
>> start throwing an error on the Q field, then users would have to
>> resort to some strange circumlocution to get around it.
>
> Hmm. That's an interesting test case: if Q throws an error, there
> doesn't seem to be any way to do it at all, because there is no format
> spec for ignoring non-constant text.
Not entirely true. It's possible, it's just not at all obvious:
=# select to_date('Wed 3rd March, Q1 2010', 'Dy FMDDTH Month, "QQ" YYYY'); to_date
------------2010-03-03
(1 row)
Anything in a format string which is quoted is ignored. Or to put it
another way, putting stuff in quotes is telling to_date() that the
characters in those positions are not important to you and should not
be used to help construct the date result. It doesn't actually check
that the characters in the source string match what you have put
inside the quotes, it just skips over the quoted number of characters.
I doubt anyone unfamiliar with the source code of the function would
ever devise the above solution, and it's an ugly hack reliant on a
quirk anyway. So a user in-the-field would probably just resort to
running a regexp_replace() over the text in order to strip out the
quarter before passing it to to_date().
> So at the moment my vote is "leave it alone". If we want to throw
> error for Q then we should provide a substitute method of ignoring
> a field. But we could just document Q as ignoring an integer for
> input.
Sounds good to me.
Cheers,
BJ