显示两个日期和日期之间的记录时间 [英] to display records between two date & time

问题描述

我有2个表客户端（client_id，client_name，client_lname）

和订单（Order_Id，Client_Id，Total_Amount，Order_Date，Order_Time，Order_Status）。我必须显示两个日期和时间之间的记录。

我写了这个查询：

 选择 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 其中（o.Order_Date   '  01/01/2013' 和 '  22/01/2013'）和（o。 Order_Time 之间的code-keyword> '  17:41:59' 和 '  20:42:04'）和 o.Order_Status = '  1' 订单 按 o.Order_Id 

它运作正常..但我不会在以下情况下工作ex.I想要查找从01/01/2013 23:12:00到22/01/2013 08:05:10的记录。如果条件是像01/01/2013 16:10:04到22/01/2013 16：20：04。帮帮我。谢谢

解决方案

为什么不使用DateTime DataType。

请指定te数据类型用于此类示例。

首先我将类型更改为



** Order_Date到目前为止和Order_Time到时间。**



然后我写了这个查询



选择o.Order_Id，c.client_name，c.client_lname，o.Total_Amount，o.Order_Date，o.Order_Time从订单作为o加入客户端作为c on o.Client_id = c.Client_id其中DATE_ADD （o.Order_Date，INTERVAL o.Order_Time HOUR_SECOND）BETWEEN''2012-12-03 11:06:54'''''2013-01-19 01:07:10''和o.Order_Status =''1' '按o.Order_Id订购



工作。说什么，我期待结果。

感谢大家帮助我解决我的问题问题

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

解决方案

Why dont you use DateTime DataType.
Kindly specify te DataTypes Used for such examples.

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.

这篇关于显示两个日期和日期之间的记录时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！