Convert database string to date in sql & laravel

Question

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();

Add proper date columns, update the new column(s) from the old columns using STR_TO_DATE(), remove the old columns, reame the new columns to be the old column names, check all the code that loaded these string dates and make sure it is now loading a date properly — RiggsFolly
– RiggsFolly, Commented May 31, 2022 at 9:05

bryanjesuistonpere · Accepted Answer · 2022-05-31 09:41:44Z

3

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

answered May 31, 2022 at 9:41

bryanjesuistonpere

564 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert database string to date in sql & laravel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related