Buy Now
Sign in

Use Current Date in Where Condition

vividal , February 02 2018, 14:28
vividal 117
February 02 2018, 14:28 #374977

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.

emmexx 440
February 02 2018, 17:58 #374985

Have you tried the following?




vividal 117
February 02 2018, 18:22 #374986

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.

vividal 117
November 24 2018, 00:37 #381754
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?
healyhatman 7
November 24 2018, 10:26 #381764
{date:} returns the UTC date.
Use {date/timezone:format}
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.
Web developer at - I DO NOT work for ChronoEngine!
Professional Work:
Custom CF / CC Plugins and Extensions:
vividal 117
November 25 2018, 00:03 #381771
This worked perfectly. Thank you!!!!