MySQL中的日期问题 [英] Date querry in MySQL
问题描述
我正在研究车辆预订系统。现在我有一辆特定的车辆预定了一段时间。比如说15-01-2013到31-01-2013。我想要的是,如果另一个用户试图预订这辆车并且他的日期介于这个范围之间,那么他绝不允许这样做。 3例可能。
1.日期从2013年7月1日到2013年1月20日。由于预订期限内的结束日期不允许。
2.日期如20-01-2013至05-02-2013。由于预订期内的开始日期不允许。
3.日期如2013年7月1日至2013年5月1日。由于预订期内的整段时间不允许。
如何在MySQL中为此编写查询?
I am working on vehicle booking system. Now I have a particular vehicle booked for a period of time. let say 15-01-2013 to 31-01-2013. What i want that if another user try to book this vehicle and his date falls in between this range, then he must not be allowed to do so. 3 cases possible for this.
1. date period like 7-01-2013 to 20-01-2013. NOT ALLOWED due to end date within booking period.
2. date period like 20-01-2013 to 05-02-2013. NOT ALLOWED due to start date within booking period.
3. date period like 7-01-2013 to 05-01-2013. NOT ALLOWED due to full period within booking period.
How can i write query in MySQL for this??
推荐答案
您将获得车辆ID知道使用,例如车辆ID为v1且预订日期在15-01-2013和31-01-2013之间
然后在程序中
you will be having vehicle id know use that for example vehicle id is v1 and the booking date is between 15-01-2013 and 31-01-2013
then in procedure
create procedure prcVehicle
(
vehicleId varchar(10),
dtBookingDate date
)
begin
declare cnt,x int;
select count(*) from tablename where fieldname=vehicleId and fieldname for date >15-01-2013 and fieldname for date <31-01-2013
if cnt>0
set x=0;
select x;
else
your insert statement
set x=1;
select x;
end if;
end
然后在前端if
then in front end if
res [''x''] = 0然后已预订其他可以预订
res[''x'']=0 then already booked else can book
这篇关于MySQL中的日期问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!