Is it necessary to cast date in mysql for comparison?

@jlrdw I have changed timezone in .env, it will store hours based on my timezone. That's not related to my question though.

@amir5 You wouldn't need:

$query->whereDate('created_at', '>', now()->startOfDay())
	->whereDate('created_at', '<', now()->endOfDay());

Just query for date and ORDER BY created_at. Or ORDER BY id DESC

@Sinnbeck Are results the same(using casts and no casts)? and if there is no difference what is the purpose of date/datetime casts in mysql when you can check data without them?

@Snapey Are results the same(using casts and no casts)? and if there is no difference what is the purpose of date/datetime casts in mysql when you can check data without them?

@amir5 casting changes the response when you use the model, or is applied to the data when saving.

It is not used in the creation of SQL queries unless you are providing the query date in some odd format.

@Snapey I mean when mysql filters data, and if there is no difference what is the purpose of date/datetime casts in mysql when you can filter data without them?

@amir5 casting has nothing to do with the mysql query. It should always be in YYYY-MM-DD format. Cast is when you want to present it to your users differently, like:

02/20/2025

But have it in the correct format to store or to query. Like:

2025-02-20

I don't even know why laravel calls it casting, it's just formatting it a certain way.

Example, when it reaches here it's in the correct format passed from controller:

    public function getChecks($offset = "", $rowsperpage = "", $checksearch = "", $searchby = null)
    {
        if ($searchby === "checkdate") {
            //$date_start = \DateTime::createFromFormat('Y-m-d', $checksearch);
            $pagingQuery = " LIMIT $offset, $rowsperpage";
            $sql = "SELECT checkid, transdate, transdescribe, widthdraw, deposit, isclr";
            $sql = $sql . " FROM checks";
            $sql = $sql . " WHERE `transdate` = :datestart";
            $sql = $sql . " ORDER BY checkid" . $pagingQuery; 
            $sth = $this->db->pdoPrepare($sql);
            $params = [':datestart' => $checksearch];
            $sth->execute($params);
            $results = $sth->fetchAll(\PDO::FETCH_ASSOC);
            return $results;
        }
        // other criteria as needed

It's from a custom framework, but the idea of passing for example 2024-12-15 as a date is the same for laravel, cakephp, java, python, etc if working with mysql.

@jlrdw Yeah, I though for doing < > = operation on dates you have to use them like where date(created_at) > xxxx, but it turns out that you don't need them, and mysql will compare them if you give a datetime with proper format like 2025-01-01 10:20:30.

@amir5 If that works, no problem.

If only one day I've always just used =:

$sql = $sql . " WHERE `transdate` = :datestart";

I haven't tried it with datetime, just dates. I might give it a try later.

EDIT:

Played around with it.

If you have date time, but only need all of one certain date:

SELECT 	`id`, 
	`name`, 
	`username`, 
	`role`, 
	`password`, 
	`remember_token`, 
	`email_verified_at`, 
	`created_at`, 
	`updated_at`, 
	`email`
	 
	FROM 
	`laravel58`.`users`
	WHERE DATE(created_at) = DATE('2019-03-27');
    /////    order by as needed   ///////

Of course in php use a parameter.

PHP PDO

            $searchdate = '2019-03-27';  // I pass from form with popup calendar 
            $sql = "SELECT id, created_at FROM users WHERE DATE(created_at) = :sch";
            $params = [':sch' => DATE($searchdate)];
            $sth = DB::getPdo()->prepare($sql);
            $sth->execute($params);
            $results = $sth->fetch(\PDO::FETCH_OBJ);
            dd($results);

Just example it works.

@amir5 You are worrying about nothing. When you need to use a cast, you will know.

@jlrdw What I've found for comparing dates to datetime columns:

where created_at >= '2012-12-12 00:00:00' and created_at < '2012-12-13 00:00:00';

where created_at >= '2012-12-12' and created_at < '2012-12-13';

where created_at >= '2012-12-12' and created_at < '2012-12-12' + INTERVAl 1 DAY;

@amir5 I would expect all of the above to work the same as my earlier eloquent example, with or without date casting.

When you recieve the result of an eloquent query, you will recieve an instance of carbon object for the created_at because by default it is cast to a datetime carbon object

ps. You missed an important option, whereBetween