Oracle converts days (1-366) to days

// In common applications, we use the date function to_date () in many places ()
// But we know little about the date function format, or we will not study it at all.
// However, knowing how important the date format is
// The following is a specific application:
// There is a table with the year field year, period field type, and period term, as shown below:
ID year type term
---------------------
1 2010 R 1
2 2010 R 2
....
....
N 2010 R 365
// Result:
Id year type term
----------------------
1 2010 R
....
....
N 2010 R
// Analysis:
// This is to convert the number of days to the date. Generally, we use to convert the string date to the date format,
// It is difficult to convert the number of days to the date;
// However, Oracle personnel have considered this issue when designing the to_date function,
// We do not need to worry about it. query the format of the to_date function,
// If a format parameter is found, the number in the range [1-366] is converted to the date format:
// DDD Day of year (1-366 ).
// The specific solution is as follows:
With t (
Select 1 id, '000000' year, 'R' type, 1 term from dual union all
Select 2, '123', 'R', 58 from dual union all
Select 3, '123', 'R', 59 from dual union all
Select 4, '20140901', 'R', 60 from dual union all
Select 5, '20180101', 'R', 2011 from dual union all
Select 6, '200', 'R', 2011 from dual union all
Select 7, '20180101', 'R', 2001 from dual union all
Select 8, '20180101', 'R', 2005 from dual union all
Select 9, '20180101', 'R', 2008 from dual)
Select id,
Year,
Type,
Term,
To_char (to_date (year | term, 'yyyddd '), 'yyyy-mm-dd') ymd
From t;
ID YEAR TYPE TERM YMD
--------------------------------------
2000 R 1 2000-01-01
2 2000 R 58 2000-02-27
3 2010 R 59 2010-02-28
4 2010 R 60 2010-03-01
5 2011 R 128
6 2011 R 129
7 2001 R 130
8 2005 R 365
9 2008 R 366