Is it possible to insert a literal character in a TO_CHAR(datetime) format? [duplicate]

Question

I'd like to select some dates in the format:

yyyydDOY

where yyyy is the 4-digit year, DOY is the day of year (1-366), and d is a literal "d". Here's what I tried:

SQL> select to_char(sysdate, 'YYYYdDDD') from dual;

TO_CHAR(
--------
20130713

Obviously that's all wrong. The result I want can be found on the command line:

$ date +'%Yd%j'
2013d071

Answer 1

First off, the code in the question is read as DDDD, which the function interprets to be DDD (the day of year: 071) followed by D (the day of week: 3).

The solution is to quote any literals with double quotation marks ("):

SQL> select to_char(sysdate, 'YYYY"d"DDD') from dual;

TO_CHAR(
--------
2013d071

See Table 3-15 Datetime Format Elements for more information.