CODEWITHSUNDEEP
phpmysqldql
Johnn
Johnn

Reputation: 25

MySQL - Querying the name of the month

I wrote a query in my Symfony that returns total sum of amounts per month of a year. But when I try to convert it from month number to month name it trows

Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ","

My code..

$resultYearly = $this->getTransactionRepository()
        ->createQueryBuilder('p')
       ->select('MONTH(p.transactionDate, \'%b\') AS MonthOfYear, sum(p.amount) Total')
MonthAndYear, sum(p.amount) Total')
            ->where('p.transactionDate >= :end')
            ->setParameter('end', new \DateTime('-1 year'))
            ->groupBy( 'MonthOfYear')
            ->getQuery()
            ->getArrayResult();

It works perfect with DATE_FORMAT but when I use MONTH it throws an error..

Upvotes: 1

Views: 68

Answers (2)

Twinkle
Twinkle

Reputation: 189

Use 

DATE_FORMAT(p.transactionDate,'%b')  AS MonthOfYear

Upvotes: 0

Ray A
Ray A

Reputation: 1341

You need to remove the part of \'%b\' from the Month function, since MONTH function takes only one parameter, which is date. So your code should be:

$resultYearly = $this->getTransactionRepository()
        ->createQueryBuilder('p')
       ->select('MONTH(p.transactionDate) AS MonthOfYear, sum(p.amount) Total')
MonthAndYear, sum(p.amount) Total')
            ->where('p.transactionDate >= :end')
            ->setParameter('end', new \DateTime('-1 year'))
            ->groupBy( 'MonthOfYear')
            ->getQuery()
            ->getArrayResult();

Upvotes: 1

Related Questions