How to change date format in laravel Query Builder from "2016-03-12" to "12-Mar-2016"

Question

How to change date-format in laravel from "2016-03-12" to "12-Mar-2016"

$results = DB::table('customers as cust')
        ->where('cust.id',$id)
        ->select("cust.*","cust.cust_dob as dob")
        ->first();

Should I use laravel raw query.

I tried this,

 ->select("cust.*","DATE_FORMAT(cust.cust_dob, '%d-%M-%Y') as formatted_dob")

Any guide on this please.

you can use raw query builder .. It will look like normal query — Exception, Mar 10 '16 at 07:10

score 34 · Answer 1 · edited Jan 07 '21 at 10:22

34

Try to use this:

$results = DB::table('customers as cust')
             ->where('cust.id',$id)
             ->select(DB::raw('DATE_FORMAT(cust.cust_dob, "%d-%b-%Y") as formatted_dob'))
             ->first();

edited Jan 07 '21 at 10:22

Rohit Suthar

3,528
1
42
48

answered Mar 10 '16 at 08:03

cronLancer

349
2
3

runs the date_format on every row, if number increases, performance will go down – Sevyls Sep 17 '18 at 09:59

score 9 · Answer 2 · answered Mar 10 '16 at 07:15

9

As there is no way except using raw query, I am using like this. It worked for me.

->select("cust.*", DB::raw("DATE_FORMAT(cust.cust_dob, '%d-%b-%Y') as formatted_dob"))

answered Mar 10 '16 at 07:15

GRESPL Nagpur

2,048
3
20
40

score 9 · Accepted Answer · answered Mar 10 '16 at 07:18

Laravel use Carbon for datetime so you can write it like following code:

$results = DB::table('customers as cust')
            ->where('cust.id',$id)
            ->select("cust.*","cust.cust_dob as dob")
            ->first();
echo $results->dob->format('d-m-Y');

Alexey Mezenin · Answer 4 · 2016-03-10T07:16:29.783

0

You can always use Carbon's ->format('m/d/Y'); to change format.

Or you can just use selectRaw to build your queries.

Also, you can try to use date mutators by settting $dateFormat to date format you want to use: https://laravel.com/docs/5.1/eloquent-mutators#date-mutators

edited Mar 10 '16 at 07:16

answered Mar 10 '16 at 07:02

Alexey Mezenin

158,981
26
290
279

devnull · Answer 5 · 2016-03-10T07:44:55.250

Laravel gives a opportunity to define accessors/mutators. Which you can use in this case rather than to do it via Queries.

i would add method in Customer model class

public function getCustDobAttribute($value) {
    return //Format the value Which represent the value in database;
}

Example: Imagine you want retrieve customer name, You want get it as First charterer capital and the rest small.

public function getFirstNameAttribute($value)
{
    return ucfirst($value);
}

Reference:

https://laravel.com/docs/5.1/eloquent-mutators#accessors-and-mutators

How to change date format in laravel Query Builder from "2016-03-12" to "12-Mar-2016"

5 Answers5