CODEWITHSUNDEEP
javamysqlprepared-statement
SatyaTNV
SatyaTNV

Reputation: 4135

Mysql between dates is not working

I'm writing the below query to get records between two dates. I'm using Mysql version 5.5. May its duplicate exactly I didn't know. But no answer working for me so that I'm asking. I'm following least date after latest date. Even though its not working.

Problem: Empty resultset.

pstmt=con.prepareStatement("SELECT urlid FROM youtubevideos WHERE lastwatched >=? AND lastwatched <=? order by id desc LIMIT 8");
pstmt.setString(1,previousdate);//14-05-2015
pstmt.setString(2,currentdate);//12-08-2015
rs=pstmt.executeQuery();
while(rs.next())
{
  .........
}

But I'm getting empty resultset.

My table youtubevideos contains records

  urlid   lastwatched
  -------------------
  url1    12-08-2015
  url2    11-08-2015
  url3    08-05-2015
  url4    
  url5    10-08-2015

Above is some data. Here lastwatched is of varchar and lastwatched is empty for some records. If my previous date 08-05-2015 means less than the current day (12) then above query working. Otherwise (from 13-05-2015 onwards) its not working. Any suggestions please.

Upvotes: 0

Views: 321

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Fix the data in the table. You should not be storing bona fide dates as varchar(). MySQL has a great data type for them, called date (or perhaps datetime.

Here is one method:

alter table youtubevideos add column NEW_lastwatched date;

update youtubevideos
    set NEW_lastwatched = str_to_date(lastwatched, '%d-%m-%Y');

alter table drop column lastwatched;

alter table rename column NEW_lastwatched lastwatched date;

Then, pass in your parameters in the ISO standard format 'YYYY-MM-DD' and your problems with dates using this column will be fixed.

Upvotes: 1

Jim
Jim

Reputation: 22656

Here lastwatched is of varchar

The issue is that you are storing date fields as type VARCHAR. This would work if your date format was Y-m-d since sorting this alphabetically is the same as sorting by date.

I recommend you change the lastwatched column to be a date type, this will allow the BETWEEN to work correctly and will also provide access to the date functions in MySQL.

Upvotes: 1

Jordi Castilla
Jordi Castilla

Reputation: 26991

You are using wrong date format for sql:

12-08-2015 // this is the output format

use yyyy-MM-dd instead:

2015-08-12 // this is the sql store format

This query works great in my Mysql database:

SELECT * FROM your_table where DATE <= "2015-05-08" AND DATE >= "2015-08-12"

To convert your strings:

Date initDate = new SimpleDateFormat("dd-MM-yyyy").parse(date);
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
String parsedDate = formatter.format(initDate);

Upvotes: 3

Related Questions