问题描述:

I need to select the row when date equal particular date, where as the TIMESTAMP column contain datetime.

This works fine,

select USERNAME from ACCESSACTIVITY where UPPER(LOCATION)=UPPER('remote-PC')and TIMESTAMP = STR_TO_DATE('28-05-2016 01:00:00','%d-%m-%Y %H:%i:%s')

where as when I avoid time, it doesn't work.

select USERNAME from ACCESSACTIVITY where UPPER(LOCATION)=UPPER('remote-PC')and TIMESTAMP = STR_TO_DATE('28-05-2016 ','%d-%m-%Y')

网友答案:

You don't need the wildcards. Try this:

select USERNAME from ACCESSACTIVITY where UPPER(LOCATION)=UPPER('remote-PC')and DATE(TIMESTAMP) = STR_TO_DATE('28-05-2016 ','%d-%m-%Y')
网友答案:

Use :

select USERNAME from ACCESSACTIVITY where UPPER(LOCATION)=UPPER('remote-PC')and TIMESTAMP LIKE STR_TO_DATE('28-05-2016 ','%d-%m-%Y')+'%'
网友答案:

Assuming timestamp is stored as a datetime, then use inequality:

select USERNAME
from ACCESSACTIVITY
where UPPER(LOCATION)=UPPER('remote-PC') and
     TIMESTAMP >= DATE('2016-05-28') AND
     TIMESTAMP < DATE('2016-05-29');

This allows the database engine to use an index on TIMESTAMP if an appropriate index is available.

相关阅读:
Top