CODEWITHSUNDEEP
phpmysqlsql
Rodrigo Pacheco
Rodrigo Pacheco

Reputation: 33

How to add leading zeros

I would like to know how can I get the leading zero's here. I already tried %H AND %h but still not working.Any help would be appreciable.Thank you

SELECT * FROM events WHERE eventDate = DATE_FORMAT(CURDATE(), '%d-%m-%Y') AND time BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 2 HOUR),'%H:%i') AND DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 10 MINUTE),'%H:%i')

UPDATE: It just doesn't work between 00:00 and 02:00.. Do someone has any ideia why it's happening??

Upvotes: 0

Views: 71

Answers (1)

Prisoner
Prisoner

Reputation: 1857

As you said your problem only exists when time between 00:00 and 02:00, it caused by this code:

DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 2 HOUR),'%H:%i')

Let's break your code into few parts:

select date_format(curdate(), '%d-%m-%Y') as date_only, 
       DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 HOUR),'%H:%i') as min2h,
       DATE_FORMAT(DATE_ADD(now(), INTERVAL 10 MINUTE),'%H:%i') as add10m

# assume now = 2016/10/28 01:00
# the date_only will return 2016/10/28
# the min2h will return 23:00
# the add10m will return 01:10

back to your query, you will have a query like this:

SELECT * FROM events WHERE eventDate = '20161028' AND time BETWEEN '23:00' AND '01:10'

That's why you can't get the result which the statement is not correct. You should convert your eventDate and time to datetime first, so that you can compare the datetime correctly when minus 2 hours is yesterday or add 10 minutes is tomorrow

select * from events 
where str_to_date(concat(`eventDate`, " ", `time`), '%d-%m-%Y %H:%i') # convert o datetime, depends on your format
between date_sub(@report_date, interval 2 hour)
and date_add(@report_date, interval 10 minute)

Fiddle

Upvotes: 1

Related Questions