CODEWITHSUNDEEP
mysqllaravel
Eden WebStudio
Eden WebStudio

Reputation: 788

How do I retrieve Month and Year from SQL query>

I have the below snippet which returns month from my created_at timestamp. I am wanting this to return as MM-YYYY but not sure of the syntax. This is in a laravel implementation. I have tried to add 'MONTH, YEAR' but no luck. 

$data = [
        'data' => Payment::selectSub('MONTH(created_at)', 'month')
        ->selectSub('SUM(payment_amount)', 'total_payments')
        ->groupBy('month')
        ->get()];

Upvotes: 0

Views: 51

Answers (2)

Hina Qaisar
Hina Qaisar

Reputation: 28

Try this

$data = [
    'data' => Payment::selectSub('MONTH(created_at)', 'year(created_at)')
    ->selectSub('SUM(payment_amount)', 'total_payments')

Upvotes: 0

user1669496
user1669496

Reputation: 33058

You want to use SQL's date_format function.

selectSub('DATE_FORMAT(created_at, "%m-%Y")', 'month')

See https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format for a full list of different formatting options.

Upvotes: 1

Related Questions