sql query to query date

Question

in table there are columns for

_year _month _day

i.e

2023 09 01

question,

how can I write the sql like this:

select...where _year_month_day < '2023-8-12'

thank you

Answer 1

You could do:

SELECT ...
FROM  tbl
WHERE datefromparts(_year, _month, _day) = '2023-08-12'

Although this will not make use of any indexes, so it will be a full scan of the table, which is not fun if the table is big.

An alternative is to add a computed column:

date AS datefromparts(_year, _month, _day)

and then index that column and use it the queries.

Answer 2

Hi @arkiboys

The datefromparts() proposed by experts is more convenient and intelligent.

Add another approach. (somewhat original)

create table test(_year int,_month int,_day int);
insert into test values
(2023,09,01),
(2023,08,13),
(2023,08,12),
(2023,08,11),
(2022,12,16),
(2022,09,01);

select * from test where 
  (_year < year('2023-08-12')) or 
  (_year = year('2023-08-12') and _month < month('2023-08-12')) or
  (_year = year('2023-08-12') and _month = month('2023-08-12') and _day < day('2023-08-12'));

Output:

Best regards,

Percy Tang

Answer 3

You could do something like this:

USE YOUR_DATABASE;
SELECT *
FROM YOUR_TABLE
WHERE _year < '2023'
AND _month < '08'
AND _day < '12';