Use Current Date in Where Condition

vi

vividal 02 Feb, 2018

I have a field (game_start - datetime type) in my table that stores the Date & Time. I want to show all records with today's date, regardless of the time. If I hard code the date and time as below I get the events that start at that time but not the others.
game_start/=:2018-02-02 19:00:00
What I am really looking for is the function/syntax to get all events for "TODAY", regardless of the time but can't seem to figure it out as I have tried a bunch of variations of date(), datetime(), now(), etc.

em

emmexx 02 Feb, 2018

Have you tried the following?

game_start:{date:Y-m-d}

bye
maxx

vi

vividal 02 Feb, 2018

I had not but that still didnt work as it created

`Schedule`.`game_start` = '2018-02-02'

which returns no results BUT it the syntax was helpful and I have implented

game_start/>=:{date:Y-m-d 00:00:00}
game_start/<=:{date:Y-m-d 23:59:59}

which produces the desires results.
thank you.

vi

vividal 24 Nov, 2018

so I thought I was all set with this months ago but didnt really check it close enough. I am not sure if my issue is a Joomla or CC6 issue. I am in the EST (-5) timezone. For some reason the site thinks it is using UTC even though I have the Joomla Website Time zone set to New York. This means that after 7pm the site thinks it is tomorrow and show events for tomorrow instead of today.

Anyone have thoughts as to what is wrong/how to fix it?

thanx.

he

healyhatman 24 Nov, 2018

Answer

1 Likes

{date:} returns the UTC date.

Use {date/timezone:format}

http://php.net/manual/en/timezones.php

Also try

DATE_FORMAT( model.game_start , "%Y-%m-%d"):{date/timezone:Y-m-d}

DON'T copy paste, DO keep the spaces around the comma, DO replace model and timezone with whatever they should be in your case.

vi

vividal 25 Nov, 2018

This worked perfectly. Thank you!!!!

Forums

Use Current Date in Where Condition