Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: MySQL
I have 2 tables client (client_id,client_name,client_lname)
and orders(Order_Id,Client_Id,Total_Amount,Order_Date,Order_Time,Order_Status).I have to display records between two dates and time.
I have wrote this query :
 
select o.Order_Id,c.client_name,c.client_lname,o.Total_Amount,o.Order_Date,o.Order_Time from orders as o join client as c on o.Client_id=c.Client_id where (o.Order_Date between '01/01/2013' and '22/01/2013' ) and (o.Order_Time between '17:41:59' and '20:42:04') and o.Order_Status='1' order by o.Order_Id 
 
It works fine..but I wont work in the following case for ex.I want to find records from 01/01/2013 23:12:00 to 22/01/2013 08:05:10 . and if the condition is like 01/01/2013 16:10:04 to 22/01/2013 16:20:04.Help me out .Thanks
Posted 22-Jan-13 2:11am
Edited 22-Jan-13 2:22am
v2
Comments
PIEBALDconsult at 22-Jan-13 8:32am
   
If your are storing dates as strings (and you should never ever ever do that), then that won't work. Likewise, the Order_Status shouldn't be a string.
Sandeep Mewara at 22-Jan-13 9:20am
   
if the condition is like 01/01/2013 16:10:04 to 22/01/2013 16:20:04
Not clear. Elaborate.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Why dont you use DateTime DataType.
Kindly specify te DataTypes Used for such examples.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

First of all I changed the type to
 
**Order_Date to date and Order_Time to time.**
 
Then I wrote this query
 
select o.Order_Id,c.client_name,c.client_lname,o.Total_Amount,o.Order_Date,o.Order_Time from orders as o join client as c on o.Client_id=c.Client_id where DATE_ADD(o.Order_Date, INTERVAL o.Order_Time HOUR_SECOND) BETWEEN '2012-12-03 11:06:54' AND '2013-01-19 01:07:10' and o.Order_Status='1' order by o.Order_Id
 
It worked.Thats what ,I was expecting the result.
Thanks everyone for helping me to solve my problem.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 325
1 OriginalGriff 278
2 Nguyen.H.H.Dang 230
3 Peter Leow 195
4 arvind mepani 187


Advertise | Privacy | Mobile
Web01 | 2.8.140709.1 | Last Updated 23 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid