sql query to query date

arkiboys 9,186 Reputation points

in table there are columns for

_year _month _day


2023 09 01


how can I write the sql like this:

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

thank you

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,602 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 93,056 Reputation points

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. PercyTang-MSFT 11,956 Reputation points Microsoft Vendor

    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
    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'));


    User's image

    Best regards,

    Percy Tang

    1 person found this answer helpful.
    0 comments No comments

  2. Paul Riley 0 Reputation points

    You could do something like this:

    SELECT *
    WHERE _year < '2023'
    AND _month < '08'
    AND _day < '12';