Separate time and search between dd/mm/yyyy dates

Asked

Viewed 919 times

-1

I have a contract table called contract two-column data_ini and data_fim.

data_ini and data_fim are as follows: 20/04/2014 10:46

I need to do a search between dates, without the time. If I didn’t have the time I could, but with the time this difficult.

I’m trying it now

SELECT DATE_FORMAT(data_ini, '%d/%m/%Y') FROM contrato
php mysql datetime

  • Buddy, I am browsing and trying right in mysql. What I found now and am testing is SELECT DATE_FORMAT(data_ini, '%d/%m/%Y') FROM contract

2 answers

4

It is recommended to use fields date for date and timestamp for date and time in your tables, date fields as varchar has several problems in sorting and searching. To get around this situation use str_to_date() that converts the string to a date, the query should look like this:

SELECT * FROM contrato_reserva 
WHERE str_to_date(data_ini, "%d/%m/%Y") 
BETWEEN str_to_date('01/01/2011', "%d/%m/%Y")
and str_to_date('20/04/2014',"%d/%m/%Y")

  • Guys, make sure it’s right. I’ll search between the two tables, data_ini and end date. (SELECT * FROM contrato_reserva WHERE str_to_date(data_ini, '%d/%m/%Y') BETWEEN str_to_date('14/09/2012', '%d/%m/%Y') and str_to_date('14/09/2012','%d/%m/%Y')) UNION ALL (SELECT * FROM contrato_reserva WHERE str_to_date(data_fim, '%d/%m/%Y') BETWEEN str_to_date('17/09/2012', '%d/%m/%Y') and str_to_date('17/09/2012','%d/%m/%Y')) Example: http://sqlfiddle.com/#! 2/3827a5/5

  • What’s the matter? What are you trying to do?

  • Does not search properly

  • Start and end dates are in different tables.

1

Try it like this:

SELECT date_format(data_criado, '%d/%m/%Y') FROM contrato WHERE (2014-04-22 >= data_ini) AND (2014-04-22 <= data_fim);

Or you can replace the date with "NOW()", which is the current date. I use this to show data that is between data_ini and end date.

Browser other questions tagged php mysql datetime

You are not signed in. Login or sign up in order to post.