CODEWITHSUNDEEP
sqloracle-database
FilipeHenriques
FilipeHenriques

Reputation: 23

Date like yyyy/yy to yyyy (ex :2016/17 to 2016)

I have a table which has a column years, and the data is like 2016/17; 2017/18, etc.

Until now I have this code:

SELECT champion 
FROM championship
WHERE season = (select to_char(sysdate - 730, 'yyyy') from dual)

I want to select, for example, the champion 2 years ago. How can i do that?

I was thinking to split 2016/17 (this is a varchar) to get only the 2016, but how?

This is a "simple" question, so, I would like to do without functions or anything like that.

Upvotes: 2

Views: 88

Answers (2)

Indent
Indent

Reputation: 4967

You can simply use substr :

SELECT champion
FROM championship
WHERE substr(season,1,4) = (to_char(sysdate - interval '2' YEAR,'YYYY') from dual)

to_char(sysdate - interval '2' YEAR,'YYYY') is better than to_char(sysdate - 730, 'yyyy'), example : 

+------------+------------+---------+
+ Date       | -730 day   + -2 year +
+------------+------------+---------+
| 2018-12-31 | 2016-12-31 + 2016    +
| 2019-12-31 | 2017-12-31 + 2017    + 
| 2020-12-31 | 2019-01-01 + 2018    + ==> different year
| 2021-12-31 | 2020-01-01 + 2019    + ==> different year
| 2022-12-31 | 2020-12-31 + 2020    +
| 2023-12-31 | 2021-12-31 + 2021    +
| 2024-12-31 | 2023-01-01 + 2022    + ==> different year
| 2025-12-31 | 2024-01-01 + 2023    + ==> different year
+------------+------------+---------+

Upvotes: 4

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

Instead of sysdate - 730, use INTERVAL years. Further, You won't require SUBSTR to extract first 4 characters if your seasons are always of the form <year/year+1>

WHERE season = 
    TO_CHAR(SYSDATE - INTERVAL '2' YEAR,'YYYY') ||'/'|| 
    TO_CHAR(SYSDATE - INTERVAL '1' YEAR,'YY')

Upvotes: 0

Related Questions