Menu
I am storing my dates in column server_date_time in varchar in dd/mm/yyyy format and i want to fetch the records lying between some dates so i have used the query
select * from activity_emp
where date_format(str_to_date(substr(server_date_time,1,10),'%d/%m/%Y'),'%d/%m/%Y')>=
'29/09/2012'
and date_format(str_to_date(substr(server_date_time,1,10),'%d/%m/%Y'),'%d/%m/%Y')<=
'07/10/2012';
I have converted varchar to string in query but my query return query data only related to 29/09/2012 and 30/09/2012. It should also return query for the month of October
407
To count the difference between dates in MySQL, use the DATEDIFF(enddate, startdate) function. The difference between startdate and enddate is expressed in days.
How to Select rows from a range of dates with MySQL query command. If you need to select rows from a MySQL database' table in a date range, you need to use a command like this: SELECT * FROM table WHERE date_column >= '2014-01-01' AND date_column <= '2015-01-01';
Query: SELECT * FROM ATM WHERE TRANSACTION_TIME BETWEEN '2005-02-28 21:00:00' AND '2008-12-25 00:00:00';
You can store date in a VARCHAR column, you can also store non-dates in that VARCHAR column.
Try with this. You can input date in dd/mm/yyyy format as in your question...
SELECT * FROM activity_emp
WHERE STR_TO_DATE(server_date_time, '%d/%m/%Y')
BETWEEN STR_TO_DATE('29/08/2012', '%d/%m/%Y')
AND STR_TO_DATE('07/10/2012', '%d/%m/%Y')
Update: I strongly recommend you to change datatype from VARCHAR to DATETIME
Cheers!!!
137
Try this one -
SELECT * FROM activity_emp
WHERE STR_TO_DATE(server_date_time, '%d/%m/%Y')
BETWEEN '2012-09-29' AND '2012-09-30'
But it is better to store server_date_time in DATETIME data type so that MySQL can use index.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
