CODEWITHSUNDEEP
mysqlselect
Hanner
Hanner

Reputation: 371

Selecting records assuming future dates in mysql

I used the following select statement to select sum of amount assuming no future dates. How do I change this statement to include future dates?

SELECT SUM(amount) as allPreviousAmount 
FROM `fn_table`
WHERE MONTH(transdate) < ? OR YEAR(transdate) < 2019 )

The ? is representing the month number. I have data in 2018, 2019 and 2020. Now I want to select all data before February 2019. How do I select it? The problem is that this also select data in 2020.

Upvotes: 0

Views: 939

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

Regards your existing query, surely you'd need AND, not OR, to ensure no future dates? Say it's July 2020 and you have a date of January 2021 which is in the future. If you say MONTH('jan-2021') < MONTH('jul-2020') OR YEAR('jan-2021') < YEAR('jul-2020') then this will be true because jan is earlier than jul so a future date of January 2021 will be returned

Not really sure why you didn't just WHERE transdate < CURRENT_DATE() unless a any day this month is also classed as a future date... (by demanding the the month and the year have to be less than the current date, if it's 13th July 2020 you'll only get records up to end of June 2020 if you consider month and year only)

But to include all dates past and future simply remove the WHERE clause entirely

Edit in response to comment:

If you want a query that gives "all data up to but not including 01 Jul 2005" then do:

WHERE transdate < ?

And in your front end supply a date type parameter of 01-jul-2005. Any date can be supplied, but to cut off at a particular month end, supply a date that is the first day of some month

You can reuse the same query for future dates too by providing a date higher than any possible date in the table, such as 01-jan-3000

Upvotes: 1

Related Questions