SQL Again

[mickjav]

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

Code Select Expand

    $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

[mickjav]

Found DATEPART but still wont run

Code Select Expand

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

[vbgamer45]

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

[mickjav]

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

I use TIME() int 10

[mickjav]

Just tried

Code Select Expand

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

[mickjav]

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

Code Select Expand

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")

[Phphelp]

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

Code Select Expand

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