Simple Machines Community Forum

I'm trying to get the total Pledges for any given month, with a limit by year.

My date is stored as an integer.

I've tried all the formats I can think of without any luck

I'm trying to get the month without leading zero for the given year

    $dbquery = $smcFunc['db_query']('', '
    SELECT date_format(p.pledge_date,"n") AS pledge_month, SUM(p.pledge_amount) As Pledged
    FROM {db_prefix}pledges AS p
GROUP BY date_format(p.pledge_date, "n")
WHERE date_format(p.pledge_date, "Y") = {int:this_year}',
            [
            'this_year' => (int)$y,
            ]
    );
Any help would be appreciated.

Mick

Found DATEPART but still wont run

SELECT DATEPART(MONTH,p.pledge_date) AS pledge_month, SUM(p.pledge_amount) As Pledged
FROM smf_pledges AS p
GROUP BY DATEPART(MONTH,p.pledge_date)
ORDER BY DATEPART(MONTH,p.pledge_date)
WHERE DATEPART(YEAR,p.pledge_date) = 2023

What is the date format in the database? unix timestamp?

Quote from: vbgamer45 on July 10, 2023, 01:43:25 PMWhat is the date format in the database? unix timestamp?

I use TIME() int 10

Just tried

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE from_unixtime(p.pledge_date,"%Y") = 2023' at line 5
File: Pledges_module.php
Line: 635


SELECT from_unixtime(p.pledge_date,"%c") AS pledge_month, SUM(p.pledge_amount) As Pledged
FROM smf_pledges AS p
GROUP BY from_unixtime(p.pledge_date,"%c")
ORDER BY from_unixtime(p.pledge_date,"%c")
WHERE from_unixtime(p.pledge_date,"%Y") = 2023

I feel so Dumb I had the Group By And Order By Above the Where  :-[  :-[

This seems to run, just need to check the numbers Thanks @vbgamer45

SELECT from_unixtime(p.pledge_date,"%c") AS pledge_month, SUM(p.pledge_amount) As Pledged
    FROM {db_prefix}pledges AS p
WHERE from_unixtime(p.pledge_date,"%Y")  = {int:this_year}
GROUP BY from_unixtime(p.pledge_date,"%c")
ORDER BY from_unixtime(p.pledge_date,"%c")

I believe you can code it like this for simplicity and acheive the same results.

SELECT
    from_unixtime(p.pledge_date, "%c") AS pledge_month,
    SUM(p.pledge_amount) AS Pledged
FROM
    {db_prefix}pledges AS p
WHERE
    from_unixtime(p.pledge_date, "%Y") = {int:this_year}
GROUP BY
    pledge_month
ORDER BY
    pledge_month