Extracting data from only the year-CodePudding

I have data in a table in SQL with dates, but how do I select only those that happen in 2021. (The dates look like 31-oct-2020) in the table. The dates are the actual date variable, not just text.

CodePudding user response：

You should avoid storing your dates as text, but rather should use a proper date column. That being said, you may check the right 4 characters of the date string:

SELECT *
FROM yourTable
WHERE RIGHT(date_col, 4) = '2021';

If the column be an actual date type then use:

SELECT *
FROM yourTable
WHERE date_col >= '2021-01-01' AND date_col < '2022-01-01';

CodePudding user response：

select * from tablename where year(datecolumn) = 2021

CodePudding user response：

I suspect that your DB is Oracle after checking out your previous post. Then you can use

SELECT *
  FROM yourTable 
 WHERE EXTRACT(year FROM dt) = 2021

SELECT *
  FROM yourTable 
 WHERE TRUNC(dt,'YYYY') = date'2021-01-01'

SELECT *
  FROM yourTable 
 WHERE dt BETWEEN date'2021-01-01' AND date'2021-12-31'

You can benefit the index if there's one on the date column(namely dt) by using the last SELECT statement