CODEWITHSUNDEEP
mysqldatebetweendate-range
mpsbhat
mpsbhat

Reputation: 2773

MySQL where clause with DATE_FORMAT

I have a MySQL table with a date column.

I am trying select all the rows between some particular date range as,

select * from myTable mt 
    where DATE_FORMAT(mt.DateCol, '%y-%m-%b') between '01/03/2015' and '09/03/2015'

where 01/03/2015 and 09/03/2015 are from and to date range selected by user which is in dd/mm/yyyy format and my datecolumn is in yyyy-mm-dd format. So how can I directly select the rows using DATE_FORMAT function. The above query gives zero result.

Upvotes: 0

Views: 39655

Answers (3)

raswanth
raswanth

Reputation: 1

It will work: table parameters is (partners_id, created_at) table => scheme is master name is partners

select partners_id,DATE_FORMAT(created_at,'%d-%m-%Y') = ('31-12-2014') from master.partners;

enter image description here

Upvotes: 0

HEMANTA
HEMANTA

Reputation: 71

date_from="2021/10/28"
date_to="2021/10/31"
query = "select * from myTable mt where mt.date between str_to_date({a}{b}{c}, '%Y/%m/%d') AND str_to_date({d}{e}{f}, '%Y/%m/%d')".format(a="'",b=date_from,c="'",d="'",e=date_to,f="'")

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

And why would you convert a date to a string for this? Just do:

select *
from myTable mt
where mt.DateCol between '2015-03-01' and '2015-03-09';

If you like, you can add date() around the constants to emphasize their types:

select *
from myTable mt
where mt.DateCol between date('2015-03-01') and date('2015-03-09');

Upvotes: 4

Related Questions