基于字段名称在sql中验证日期和时间 [英] validateion for date and time in sql based on field name

问题描述

在我的表中，我有一个表中的四个字段，即项目，预订说明，bookeStartDate，预订结束日期



现在如果项目字段是卡车一个，且预订的开始日期是说2012年12月23日下午4:00和预订的日期日期是说2012年12月23日6:00



现在卡车一个有这次预定了。所以我再也不能在同一个日期时间预订同一辆货车（即货车一辆）。但是如果项目字段是卡车，则两个人想在相同的日期和时间预订它。



如何给出sql查询以及如何在asp中实现它。净。我的查询不是很好。



谢谢

Hi,
In my table i have four fields in a table namely Item,booking description, bookeStartDate,bookedEndDate

now if the item field is lorry one and bookedStartDate is say 23/12/2012 4:00pm and bookedEndDate is say 23/12/2012 6:00

now lorry one has booked this time. so again I cant book the same lorry(ie lorry one) at the same date time. but if the item field is lorry two wants to book at the same date and time it can do.

how can i give sql query and how to inplement it in asp.net. Im not that great in querying.

Thanks

推荐答案

在插入之前，您需要重新检查检查条件或运行的查询声明如果您在插入声明之前使用任何验证。



您是否忘记将日期添加到您的选择查询中？



我相信你的验证查询应该如下所示



从your_table中选择计数（*）其中item ='''lorry''和reservationsEndDate =''enddate''和expectedStartDate =''startDate''



或



select count（ *）来自your_table，其中item ='''lorry''和expectedStartDate> =''startDate''和reservationsEndDate< =''enddate''



尝试也包括时间。

您也可以参考以下链接。

http://www.databasejournal.com/features/mssql/article.php/10894_2209321_2/Working -with-SQL-Server-DateTime-Variables-Part-Three --- Search-for-specific-Date-Values-and-Ranges.htm [ ^ ]

You need to revisit your checking condition or the query you are running before the insert statement If you are using any validation prior to the insert statement.

were you forget to add the dates to your select query?

I believe your verification query should be something like below

select count(*) from your_table where item=''lorry'' and bookedEndDate =''enddate'' and bookedStartDate = ''startDate''

or

select count(*) from your_table where item=''lorry'' and bookedStartDate >= ''startDate'' and bookedEndDate <=''enddate''

try to include the time also.
You may also refer the following link.
http://www.databasejournal.com/features/mssql/article.php/10894_2209321_2/Working-with-SQL-Server-DateTime-Variables-Part-Three---Searching-for-Particular-Date-Values-and-Ranges.htm[^]

这篇关于基于字段名称在sql中验证日期和时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！