Is it possible to insert a literal character in a TO_CHAR(datetime) format?
oracle, plsql, sql
Solution
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.
Problem
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 ```