How to get number of years from PHP date range
I have a number of dates using date ranges in date format. Now I would like to have only years from that date.
Let's say someone gives a date range from: 01-01-2010 to 09-05-2019.
I only want to have years
from that date, so I will include them in my SQL query.
The query only works 2015
if the ranges are given this year, but how can I use it for all years only within the ranges?
Here is the request:
$sql = "SELECT
MONTH (transaction_date),
SUM(case when finance_type != 'Deposit' then amount else 0 end) AS total_amount,
SUM(case when finance_type = 'Deposit' then amount else 0 end) AS deposit,
SUM(case when finance_type != 'Deposit' AND amount < 0 then amount else 0 end) AS expenses,
SUM(case when finance_type != 'Deposit' AND amount > 0 then amount else 0 end) AS earning,
SUM(case when finance_type != 'Deposit' AND amount!='' then num_contract else 0 end) AS total_contracts,
transaction_date
FROM
`tbl_finanace`
WHERE
transaction_date LIKE '%2015%'
AND user_id = '15'
GROUP BY
MONTH (transaction_date)
ORDER BY
MONTH (transaction_date)";
+3
Keep coding
source
to share
2 answers
You can use less and more than you would with this DATETIME field.
where transaction_date <= '2010-01-01 00:00:01' and transaction_date >= '2005-12-31 23:59:59'
+2
Tech savant
source
to share
You can delete the sentence LIKE
in the date and use
WHERE YEAR(transaction_date)
BETWEEN YEAR('01-01-2010') AND YEAR('09-05-2019')
+1
Hanky Panky
source
to share