I’m trying to SELECT rows that matches datetime in my column. I have a table containing mDTS set as DATETIME (with no curly braces).
My table looks something like this:
|1||10/08/2021 10:41:47||11/08/2021 10:41:47|
|2||12/08/2021 10:42:34||13/08/2021 10:42:34|
CREATE TABLE tb_cyc ( mID int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID cycle', mDTS datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Data inizio ciclo', mDTE datetime DEFAULT '0000-00-00 00:00:00' COMMENT 'Data fine ciclo', PRIMARY KEY (mID) )
I’m trying to run the following query but it returns an empty set.
SELECT * FROM tb_cyc WHERE mDTS = '12/08/2021 10:42:34'
I’ve also tried:
SELECT * FROM tb_cyc WHERE mDTS LIKE '12/08/2021 10:42:34'
SELECT * FROM tb_cyc WHERE mDTS = '12/08/2021 %'
But none of this seems to work.
What am I doing wrong?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Change the format of the date in WHERE clause:
WHERE mDTS = '2021-08-12 10:42:34'
What happens is that
mDTS is a datetime; when compared with a string MySQL will treat the string as a date/time. The literal value
12/08/2021 10:42:34 will generate the following warning:
Incorrect datetime value: '12/08/2021 10:42:34' for column 'mDTS' at row 1