Is it possible to insert an alphabetic character in TO_CHAR (datetime) format?

Question

I would like to select several dates in the format:

yyyydDOY 

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

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

Obviously, everything is wrong. The result I want to find can be found on the command line:

 $ date +'%Yd%j' 2013d071 

Answer 1

First, the code in the question reads as DDDD , which the function interprets as DDD (day of the year: 071), and then D (day of the week: 3).

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

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

See Table 3-15 Date and Time Format Elements for details.