CODEWITHSUNDEEP
sqloracle-database
davidij
davidij

Reputation: 13

SQL query that returns a date

My DB contains a period(month) and a year - I am trying to convert it to a date. I don't care for the actual day of the month so I have just made it "1" (the 1st of the month).

In my code I had to convert the "13th" period to the 12th because of the 12 months of the year, so my decode did that part... Ultimately, I want it to return as a date. I have a concatenation to make it 'look' like a date, but not actually a date.

What i do with the data is query it and return it in Excel for further manipulation. When imported to Excel, it does not import as a date nor does it let me convert to a date format.

SELECT DIA_PROJECT_DETAIL.FY_DC || '/' || 
   decode(DIA_PROJECT_DETAIL.PER_DC,1,1,2, 2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10,11,11,12,12,13,12)||
   '/01' as "Date"
FROM AMS.DIA_PROJECT_DETAIL DIA_PROJECT_DETAIL

There has to be an easier or more effective way to do this!

Upvotes: 1

Views: 69

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

There is no much simpler way. DECODE is fine for converting month 13 to month 12, but you use it a bit too complicated. Then you shouldn't rely on session settings, but explicitly tell the DBMS the date format your assembled string represents. Use TO_DATE with the appropriate format.

select 
  to_date(fy_dc || to_char(decode(per_dc, 13, 12, per_dc), '00') || '01', 'yyyymmdd') 
    as "Date"
from ams.dia_project_detail dia_project_detail;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Just use least():

SELECT (DIA_PROJECT_DETAIL.FY_DC || '/' || 
        LEAST(DIA_PROJECT_DETAIL.PER_DC, 12) ||
        '/01'
       ) as "Date"
FROM AMS.DIA_PROJECT_DETAIL DIA_PROJECT_DETAIL;

Upvotes: 0

Related Questions