problem comparing two dates in mysql

Question

I m using below query from c# to compare current date with date stored in database as string.but it do not show the proper output even though it is not showing any error.

Select * from tblconcertdetail 
WHERE STR_TO_DATE(Concert_Date,'%m/%d/%Y')>="+DateTime.Now.ToString("yyyy-MM-dd")+";

first argument shows as: 2011-06-10 second shows ; 2011-06-02

it shows all rows of table

What happens if you directly input the date that you are comparing the STR_TO_DATE output to, e.g., STR_TO_DATE(...) >= '2011-06-02' — tofutim
– tofutim, Commented Jun 2, 2011 at 7:57
Have you tried: Select * from tblconcertdetail WHERE STR_TO_DATE(Concert_Date,'%m/%d/%Y')>= curdate() ? — Nicola Cossu
– Nicola Cossu, Commented Jun 2, 2011 at 7:59
PS: 2011-06-10 is greater than/equal to 2011-06-02. This row should be displayed. — Salman Arshad
– Salman Arshad, Commented Jun 2, 2011 at 8:08

Rakesh Sankar · Accepted Answer · 2011-06-02 08:03:22Z

1

Are you sure the first argument returns as you said YYYY-MM-DD???? Make sure the value for "Concert_Date" should look like "04/22/2011".

Try the following:

Select * from tblconcertdetail where date(Concert_Date) >= "+DateTime.Now.ToString("yyyy-MM-dd")+";

I think Concert_Date is of datatype "date" or "datetime" or "timestamp".

answered Jun 2, 2011 at 8:03

Rakesh Sankar

9,4255 gold badges45 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user755230 Over a year ago

no i have taken Concert_Date as string type for easyness at time of inserting data

Salman Arshad Over a year ago

@user755230: if it were possible to downvote a comment I would have done so!

GSerg · Accepted Answer · 2011-06-02 23:03:58Z

0

The output of STR_TO_DATE(Concert_Date,'%m/%d/%Y') depends on Concert_Date. Its different values will produce different results if compared to the second date.

I think you should try to use this portion of code:

STR_TO_DATE('2011-06-10','%Y-%m-%d');

IMHO if Concert_Date is equal to 2011-06-10 you cannot read it correctly with '%m/%d/%Y' patern.

edited Jun 2, 2011 at 23:03

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

answered Jun 2, 2011 at 8:15

Salaros

1,4521 gold badge14 silver badges36 bronze badges

Collectives™ on Stack Overflow

problem comparing two dates in mysql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related