command object help

[Former Member]

Hi All,

I have a report having command object like this.My back end is MYSQL.

I am not getting data in designer with below query

SELECT * FROM clock_time WHERE STR_TO_DATE(DATE_FORMAT(clock_time,'%d-%m-%Y %H:%i'),'%d-%m-%Y %H:%i')= DATE_FORMAT('2014-06-19 11:03:43','%d-%m-%Y %H:%i')

What is the mistake I am doing .

Please help.

[abhilash_kumar · Accepted solution]

Hi Divya,

Does the same command work on the database when run via the database client?

See if this works:

SELECT * FROM clock_time WHERE DATE_FORMAT(clock_time,'%d-%m-%Y %H:%i') = Str_To_Date('19-06-2014 11:03','%d-%m-%Y %H:%i')

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

I tried like this and working fine in both designer and database.Is this correct ?

Please suggest

select * from clock_time where DATE_FORMAT(clock_time,'%Y-%m-%d %H:%i')=DATE_FORMAT({?Date},'%Y-%m-%d %H:%i')

[abhilash_kumar · Accepted solution]

Yes, if it works fine in both the Designer and the database, then this is correct.

-Abhilash