Click here to Skip to main content
11,493,154 members (61,916 online)
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 3:11am
Edited 22-Jan-13 3: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 Sergey Alexandrovich Kryukov 419
1 Maciej Los 286
2 F-ES Sitecore 224
3 KarstenK 210
4 CPallini 208
0 Sergey Alexandrovich Kryukov 10,132
1 OriginalGriff 8,630
2 Sascha Lefèvre 3,624
3 Maciej Los 3,392
4 Richard Deeming 2,500


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100