Select month from a date field in laravel

Question

I'm trying to perform a query in Laravel 7 that gets the month part of a date field. I tried the following queries

$test = MyModal::selectRaw('month("date_col") as temp')->get();
$test = MyModal::select(DB::Raw('month("date_col") as temp'))->get();
$test = DB::table('my_table')->select('month("date_col") as temp')->get();

All variation of the same query. If I dump the query log, the query is correct for all 3

select month("date_col") as temp from "my_table"

If I run this on my DB I get the result as well. But I keep getting this error in laravel:

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 no such function: month (SQL: select month("date_col") as temp from "my_table")

Am I doing something wrong? And is there a way to do this query in laravel?

Edit: Sorry forgot to mention this is with Laravel's unit testing

score 1 · Answer 1 · answered Sep 21 '20 at 19:02

1

I think the problem in the way you use Month function

you don't set quotations for the column name, it should be:

$test = MyModal::selectRaw('month(date_col) as temp')->get();

it should work

answered Sep 21 '20 at 19:02

OMR

11,736
5
20
35

i did try different variations as well that includes with and without quotes as well as using all caps "MONTH()" – Justin S Sep 21 '20 at 19:08
sorry forgot to mention that this was with laravel's unit testing. Not sure i that makes a difference – Justin S Sep 21 '20 at 19:12
1

i think you adjust your test to sqLite db not my sql .. in sqlite you could do it like: select strftime('%m', dateField) as Month – OMR Sep 21 '20 at 19:23
i recommend to adjust your db for test to the same db type you use in you prog – OMR Sep 21 '20 at 19:24
your answer worked for me but you were right that I was using sqlite for testing and mysql as DB so the solution works for tests only. Do you know how to update to use inmemory mysql for tests for laravel? Thanks – Justin S Sep 22 '20 at 16:50
1

take a look on: https://stackoverflow.com/a/41482758/10573560 – OMR Sep 22 '20 at 17:33

score 0 · Answer 2 · answered Sep 21 '20 at 19:06

0

You can do something like this:

 MyModal::select(DB::raw("MONTH(date_col) month"))->get();

answered Sep 21 '20 at 19:06

Leo Ferreira

1
2

sorry forgot to mention that this was with laravel's unit testing. Not sure i that makes a difference – Justin S Sep 21 '20 at 19:13

Justin S · Answer 3 · 2020-09-22T16:48:41.917

Thanks to OMR's comment this solution finally worked

$test = MyModel::selectRaw('strftime("%m", "date_col") as temp')->get();

Update: It looks like what I had in the beginning worked fine and the unit test was the only part throwing this error. Since I was working with mysql while the test environment was using sqlite which apparently doesnt like the "month()" function which OMR did point out in his comment. So this solution works in the test but not in live. Couldn't figure out how to change the test environment to use in memory mysql instead of sqlite. If anyone knows how to please add an answer here.

Select month from a date field in laravel

3 Answers3