Both questions were prompted by a recent question on this forum: Months between two dates concatenated based on year quarter However, it is not necessary to read that thread to understand my questions. Also, apologies for asking two different questions in a single thread; they are so small that it didn't make sense to me to start TWO threads for them.
1.
TO_DATE() and TO_CHAR() allow boilerplate text (string literals) enclosed in double-quotes. For example:
select TO_DATE('Y2018M10D05', '"Y"yyyy"M"mm"D"dd') from dual;QUESTION: It seems obvious that the string literal can't contain the double-quote character " ( chr(34) ). And experimentation shows that this is indeed the case. Is there any way to escape a double-quote within a string literal enclosed in double-quotes?
In the linked thread, it would have been nice to be able to output a date in string format, enclosed in double-quotes. Something like
TO_CHAR(sysdate, '""""yyyymm""""')
to get the string "201811" (including the double-quotes), assuming that two consecutive double-quotes in a double-quoted string are resolved to a single, literal double-quote. Alas, that doesn't work: what is returned is just 201811, without the double-quotes.
Escaping with a backslash is even worse; perhaps not unexpectedly, TO_CHAR(sysdate, '"\""yyyymm"\""') returns the string \yyyymm\
An obvious workaround would be to use a different character in the boilerplate text and then use TRANSLATE() - or to concatenate the double-quotes after the fact. My question is, can such workarounds be avoided?
The same question applies to TO_DATE(), when the input has double-quote characters embedded as (part of) boilerplate text. How can TO_DATE() be applied in such cases? Or do we need to change or remove the double-quote character using string functions, before applying TO_DATE()?
2.
TO_CHAR(sysdate, 'YYYYQ') will return 20184 - the year and the calendar quarter. I know that TO_DATE('20184', 'YYYYQ') will not work - it's in the documentation, and experimentation confirms.
ORA-01820: format code cannot appear in date input format
My question is, WHY? I guess I can add this to the Oracle suggestion box, but I was wondering if anyone knows a good reason why Oracle made this choice.