6

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
Jon 'links in bio' Ericson
  • 20,880
  • 12
  • 98
  • 148

1 Answers1

19

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.

Jon 'links in bio' Ericson
  • 20,880
  • 12
  • 98
  • 148