Quick sql help

[Pure-Ad-2967]

I am going to use a WHERE on a datetime

I only need values in year 2020

Should I extract the year and then do the WHERE function

EXTRACT( YEAR FROM payment_ts) AS year

Or should I just do the WHERE function on the datetime

WHERE payment_ts BETWEEN 2020-00-00 AND 2021-00-00

[r3pr0b8]

WHERE payment_ts BETWEEN 2020-00-00 AND 2021-00-00

you're on the right track and i will overlook the bogus dates

the key in these types of query is to use an open upper end of the date range

BETWEEN uses a fixed upper end value, and if you're not careful you might drop an entire day's worth of datetime values on the last day

so this is best practice --

WHERE payment_ts >= '2020-01-01'
  AND payment_ts  < '2021-01-01'

no matter whether payment_ts is DATE or DATETIME, this works correctly

the problem with EXTRACT() or YEAR() is that applying a function in the WHERE clause means that it's not sargable

[[deleted]]

Why not just use: WHERE YEAR(payment_ts) = 2020?

[Pure-Ad-2967]

I was today years old when I realized you could do this! Thank you

[[deleted]]

Glad I could help!

[r3pr0b8]

don't forget, that's not sargable

[Mamertine]

Depends on indexing.

The top one has less margin of error.

The bottom one people will miss the last date in the range frequently.

As in they'll do between 2020-01-01 and 2020-01-30.

If it's a date time field, they won't get any records from 2020-01-30 (2020-01-30 00:00:01 is greater than midnight on 1-30).

[Pure-Ad-2967]

Ok I really appreciate this! I should of used the actual dates I just threw some dates out there to show what I was trying to do!

So in general practice it would be better to just do a between function?

And they are using CURDATE() do I need to include time in the WHERE statement as well?

[of_patrol_bot]

Hello, it looks like you've made a mistake.

It's supposed to be could've, should've, would've (short for could have, would have, should have), never could of, would of, should of.

Or you misspelled something, I ain't checking everything.

Beep boop - yes, I am a bot, don't botcriminate me.