CODEWITHSUNDEEP
phplaravelstringdate
swapnil mane
swapnil mane

Reputation: 257

Convert database string to date in sql & laravel

I have made a problem here. I have stored date into the database in a string format but now I am finding records on the basis of dates. and it is not working. What is the solution to this problem

datatype : varchar | data : eg. 01-10-2002

$current_month_balance =
      tbl_wallet_detail::
      whereMonth('start_date', date('m'))
      ->whereYear('start_date', date('Y'))
      ->get();

Upvotes: 0

Views: 721

Answers (1)

bryanjesuistonpere
bryanjesuistonpere

Reputation: 56

One solution is to change database type of that column to date, and i would say that if possible that would be the correct solution.

If this is not possible, you could try something like this (based on the date format that you provided):

$current_month_balance =
  tbl_wallet_detail::
  whereMonth(DB::raw("STR_TO_DATE(start_date, '%d-%m-%Y')"), date('m'))
  ->whereYear(DB::raw("STR_TO_DATE(start_date, '%d-%m-%Y')"), date('Y'))
  ->get();

This will search a raw DB statement instead of an attribute. Tested on MySQL

Upvotes: 3

Related Questions