CODEWITHSUNDEEP
mysql
user5372077
user5372077

Reputation:

how to fetch data between two dates(From Date, To Date) tables in MYSQL?

Each date as one table in my database.In all Tables data is same but table is different. I want to search one column (this column is common for all tables).I passed dates like 2015-01-01 as from date and 2015-05-31 as to Date

I wrote the query like this but this is not working for me.

select MsgId, UserId, Priority, MsgStatus, 

FROM abc.`2015-05-04` as p1,abc.`2015-05-14` as p2 
WHERE  Id="cfcbc375-372e-4586-bc23-9e94e6eb2c16"

Upvotes: 2

Views: 539

Answers (4)

Piyush Gupta
Piyush Gupta

Reputation: 2179

You can achieve with append functionality Like this, Here we are taking from date to till date and after that converting date type to string format. now calling sql query till last date then use append functionality like finalQuery = finalQuery + " " + " union all " + " " + query1; Hope You will get data from start date to end date. :)

finalQuery = "SELECT  TransDate FROM `" + str_date + "` WHERE `Id`='" + Id 

for(int i=1;i<dates.size();i++){
   Date lDate =(Date)dates.get(i);
   ds = formatter.format(lDate);    


  for(int i=0;i<s;i++) {
      query1 = "SELECT  TransDate FROM `" + ds + "` WHERE `Id`='" + Id + "'";         
  }
  finalQuery = finalQuery + " " + " union all " + " " + query1;
}

Upvotes: 0

HardLeeWorking
HardLeeWorking

Reputation: 195

to search for a date use to_date

and >= to_date('22-12-2015','dd-mon-yyyy')
and <= to_date('21-01-2016','dd-mon-yyyy')

this would look for all instances where the date was between 22nd December and 21st January inclusive.

This is assuming that OP is misunderstanding the FROM part of an SQL statement, the dates should be in the WHERE/AND clauses.

Upvotes: 1

Phiter
Phiter

Reputation: 14982

I see. You have one table for each date, every table contains the same columns.

That's not how you design tables, it's bad programming.

You must create a new column in one table and add the dates into this column.

Then you can use it as: 

select MsgId, UserId, Priority, MsgStatus, 
       DestinationNumber, OrginatorName, MsgSubmitID,
       MsgStatusMsg, TransDate, ClientMsgId 
FROM abc.`table`
WHERE  Id="cfcbc375-372e-4586-bc23-9e94e6eb2c16" AND
Date BETWEEN 'date1' and 'date2'

See Between operator

Upvotes: 0

Vipin Jain
Vipin Jain

Reputation: 3756

You can use UNION ALL 

select MsgId, UserId, Priority, MsgStatus, 
       DestinationNumber, OrginatorName, MsgSubmitID,
       MsgStatusMsg, TransDate, ClientMsgId 
FROM abc.`2015-05-04` as p1
WHERE  Id="cfcbc375-372e-4586-bc23-9e94e6eb2c16"

UNION ALL

select MsgId, UserId, Priority, MsgStatus, 
       DestinationNumber, OrginatorName, MsgSubmitID,
       MsgStatusMsg, TransDate, ClientMsgId 
FROM abc.`2015-05-14` as p2 
WHERE  Id="cfcbc375-372e-4586-bc23-9e94e6eb2c16"

you should use union all for all table between 2015-05-04 and abc.2015-05-14 like 2015-05-04, 2015-05-05, 2015-05-06 and so on

Upvotes: 0

Related Questions