how to convert date format from 2021-03-18 14:34:34' to '2021-03-18 00:00:00 in mysql-CodePudding

tring to use date filter for same date the date stored in datebase for column MODIFIED_DATE is '2021-03-18 14:34:34' format
The where clause condition for filter is "where (MODIFIED_DATE BETWEEN '2021-03-18 00:00:00' AND '2021-03-18 00:00:00')" but it is unable to fetch the records example query:

select MODIFIED_DATE
from instance_history  where (MODIFIED_DATE BETWEEN '2021-03-18 00:00:00' AND '2021-03-18 00:00:00') ;

it is showing record not found

i have used this method but it does not helped cast(cast(left(PIH.PROCESS_MODIFIED_DATE, 10) as date) as datetime) as PROCESS_MODIFIED_DATE but it does not worked

CodePudding user response：

See below sample

select current_timestamp, date_format(current_timestamp(), "%Y-%m-%d 00:00:00") 'ExpectedDate format';

current_timestamp | ExpectedDate format

2022-07-29 17:12:19 | 2022-07-29 00:00:00

Replace current_timestamp() with Modified_Date

CodePudding user response：

Do not use BETWEEN. Use 2 separate conditions (@date is needed value, for example, '2021-03-18'):

SELECT modified_date
FROM instance_history  
WHERE modified_date >= @date 
  AND modified_date < @date   INTERVAL 1 DAY) ;

When date literal is used then the timepart is '00:00:00'.