SQL: USING TIMESTAMP in WHERE CLAUSE in SQL

Question

I need to filter the results to see 2023 data only. But my WHERE clause also captures data in 2024 and I'm not sure what's causing this issue.. Anyone know what I need to fix in order to just view results in 2023? Btw - dates are shown as string in my dataset.

SELECT id, name, performance, record_date
FROM records
WHERE date_parse(record_date,'%Y-%m-%d') >= DATE_ADD('year', -1, TIMESTAMP '2024-01-01 00:00:00')

Thank you.

Answer 1

You don't need to parse date or dateadd to get data for 2023. Simply check the year:

SELECT id, name, performance, record_date
FROM records
WHERE year(record_date) = 2023;

EDIT: What if it is a string, and\or required to operate on a date range?

Then parse the date and still check the year. ie:

SELECT id, name, performance, record_date
FROM records
WHERE year(date_parse(record_date,'%Y-%m-%d')) = 2023;

If need a date range check, for example all in January, Feb and March in 2023:

SELECT id, name, performance, record_date
FROM records
WHERE date_parse(record_date,'%Y-%m-%d') >= date_parse('2023-01-01','%Y-%m-%d')
  and date_parse(record_date,'%Y-%m-%d') < date_parse('2023-04-01','%Y-%m-%d');

Note that we want all up to excluding April 1st, 2023 midnight.