使用日期格式编写过程的示例 [英] example for writing procedure using date format

问题描述

你好，
任何人都可以帮助我编写从特定日期到特定日期的休假申请程序，在该过程中不应重复从开始"到结束"的日期.
示例:
如果我要申请从2011年9月9日至2011年10月10日的假期，则不应允许介于和之间的日期再次申请假期.就像我不应该再次申请"2011年2月9日至2011年11月11日"或"2011年11月31日至2011年11月11日"请假.但是我可以根据员工代码和申请的休假日期申请一天的休假.
您能告诉我此特定细分的逻辑吗???
我已经写了类似这样的逻辑代码..

Hello,
Can anyone help me in writing the procedure for applying leaves from particular date to particular date, where there should not be any repeating of the dates of from and to.
Example:
If i am applying the leave from 01-09-2011 to 10-09-2011 then the dates in between and dates before should not be allowed to apply again for leave. like i should not be able to apply the leave again "02-09-2011 to 11-09-2011" or "31-08-2011 to 11-09-2011". But i can apply as many leaves for the day based on the employeecode and applied leave date.
Can you tell me the logic for this particular segment...??
I have written the logic code something like this..

if not exists(select * from [dbo].[LeaveForm_Details] where EmpCode = @pEmpCode and convert(varchar,AppliedDate,103) = convert(varchar,convert(datetime,@pAppliedDate,103),103)	
and (convert(datetime,@pFromDate,103) >= convert(datetime,FromDate,103) 
and convert(datetime,@pFromDate,103) <= convert(datetime,ToDate,103)) 
and (convert(datetime,@pToDate,103) >= convert(datetime,FromDate,103) 
and convert(datetime,@pToDate,103) <= convert(datetime,ToDate,103)))

if not exists(select * from [dbo].[Tt_LeaveForm_Details] where EmpCode = @pEmpCode and convert(varchar,AppliedDate,103) = convert(varchar,convert(datetime,@pAppliedDate,103),103)	
and (convert(datetime,@pFromDate,103) <= convert(datetime,FromDate,103) 
and convert(datetime,@pFromDate,103) >= convert(datetime,ToDate,103)) 
and (convert(datetime,@pToDate,103) <= convert(datetime,FromDate,103) 
and convert(datetime,@pToDate,103) >= convert(datetime,ToDate,103)))

您能以预期的输出进行修改吗?

Can You please modify it with the expected output.
Thanks.

推荐答案

处理日期的规则之一:永远不要将它们存储为字符串.始终将它们存储为Date或DateTime，这样就可以对它们进行数学运算，而不必担心混合日期格式.

更改数据库，并改用正确的日期.

然后可以使用BETWEEN子句:

Rule one of handling dates: Never, ever store them as strings. Always store them as Date or DateTime, that way you can do maths on them, and you don''t have to worry about mixed date formats.

Change your database, and use proper dates instead.

Then you can use the BETWEEN clause:

SELECT * FROM myTable WHERE NOT ((requestedStart BETWEEN startDate AND endDate) OR (requestedEnd BETWEEN startDate AND endDate))

将返回重叠的记录.

这篇关于使用日期格式编写过程的示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！