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:

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'

and

SELECT * FROM tb_cyc WHERE mDTS = '12/08/2021 %'

But none of this seems to work. What am I doing wrong?

CodePudding user response：

Try:

   CREATE TABLE test_tbl    (
                mID int(9),
            mDTS varchar(40) );


    INSERT INTO test_tbl VALUES
                          (1,'10/08/2021 10:41:47'),
                          (2,'12/08/2021 10:42:34');

select mID,
      mDTS,
str_to_date(mDTS,"%d/%m/%Y %h:%i:%s") as my_date 
from test_tbl 
where str_to_date(mDTS,"%d/%m/%Y %h:%i:%s")>='2021-08-12 10:42:34';

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/110

CodePudding user response：

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