CODEWITHSUNDEEP
mysqldate
user2979046
user2979046

Reputation: 183

select a date lesser than a particular date

I have stored the dates as string in my database.I know it is not good,but project already has been half developed before i take over and where dates were stored as string,so i was continuing the same way.

Now i want to select dates from table where date is greater than a specific date.

I tried the following query

SELECT
 *
FROM
 dates 
where
 STR_TO_DATE(date, '%Y-%m-%d') > "2014-01-01"

but it is not returning only greater values. Please help me to solve problem.

Demo

Upvotes: 1

Views: 848

Answers (3)

Ben
Ben

Reputation: 401

What type has the date? I'd prefer a ' instead of " for strings in SQL. Let's assume that date is a VARCHAR or TEXT field (depending on which database you are using):

SELECT *
  FROM dates 
 WHERE STR_TO_DATE(date, '%Y-%m-%d') > STR_TO_DATE('2014-01-01', '%Y-%m-%d')

If date is a real DATE

SELECT *
  FROM dates 
 WHERE trim(date) > STR_TO_DATE('2014-01-01', '%Y-%m-%d')

Or you just convert it into a number format date_format(date,'%Y%m%d') > 20140101

Upvotes: 0

Guest
Guest

Reputation: 1

Convert everything to date and it should be fine. Now you are comparing date and string.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Your dates are not in YYYY-MM-DD format. Use the right format!

SELECT *
FROM dates 
where STR_TO_DATE(date, '%m-%d-%Y') > date('2014-01-01')

If you are going to store dates as strings, then the best way is in the ISO format of YYYY-MM-DD.

You should read the documentation on str_to_date() (here).

Upvotes: 3

Related Questions