Laravel's whereMonth and SQLite

February 7, 2018

Just a quick one about a problem I was facing today.

Trying to use Laravel's whereMonth() methods that is available in the Query Builder I was facing an issue during testing where this method was not working. I was just trying to do a simple filter to find all blog posts written within a certain month of the year and had something similar to this in a query scope.

public function scopeByMonth($query, $month)
{
    $query->whereMonth('published_at', $month);
}

// Then use like this to get all posts published in February
Post::byMonth(2)->get(); 

I always start testing with an in memory SQLite database for speed reasons and for some reason this was not working. I tried switching to a MySQL database and it worked. It turns out that SQLite requires a single digit month number to prefixed with a '0'. So I just had to change my query scope to be:

public function scopeByMonth($query, $month)
{
    $query->whereMonth('published_at', str_pad($month, 2, '0', STR_PAD_LEFT));
}

Using str_pad() meant that any number passed through would now be prefixed with a 0 if it was less than 2 characters long. E.g. '2' would become '02', but '12' would stay as '12'.