SQL query date string by range

Question

Spec: I need to query by date month and day only (ignoring year)

Input:

start := '2015-12-29'
end := '2016-01-03'

Output:

1986-12-30
1980-01-01
1982-12-31
1978-01-03
...

Current solution:

SELECT *
FROM users
WHERE RIGHT(birthday,5) >= $1
  AND RIGHT(birthday,5) <= $2

But this only works when the year not overlap, what's the easiest solution for this case?

My simplest solution is by generating the SQL string using another language:

if start_year == end_year {
    date_where = `
    AND RIGHT(birth_date,5) >= RIGHT(` + Z(start) + `,5)
    AND RIGHT(birth_date,5) <= RIGHT(` + Z(end) + `,5)
    `
} else {
    date_where = `
    AND ( RIGHT(birth_date,5) >= RIGHT(` + Z(start) + `,5)
        AND RIGHT(birth_date,5) <= '12-31'
    ) OR ( RIGHT(birth_date,5) >= '01-01'
        AND RIGHT(birth_date,5) <= RIGHT(` + Z(end) + `,5)
    )`
}

I believe there are better way than this..

Frank Heikens · Accepted Answer · 2015-03-06 09:30:20Z

1

A date is not a string, don't use string functions to handle a date. Use date functions like EXTRACT:

SELECT  '2015-12-29'::date,
    EXTRACT(month from '2015-12-29'::date),
    EXTRACT(day from '2015-12-29'::date);

answered Mar 6, 2015 at 9:30

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kokizzu Over a year ago

btw I'm using JSONB, so it's a TEXT/string, not a DATE, I just simplify the question so it doesn't look like a JSONB..

Frank Heikens Over a year ago

When you know it is a date, cast it to a date and handle it like a date.

Collectives™ on Stack Overflow

SQL query date string by range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related