验证sql server中的日期 [英] Validate the date in sql server

问题描述

我将树叶存放在一个表中，其中开始日期和结束日期仅为日期部分，时间存储为上午和中午之后的FN和AN，



例如存储一整天的假期如下：



Fromdate：2016-03-03，fromLeaveTime：'全天'

Todate：2016-03-03，toLeaveTime：'FN'


来自LeaveTime和toLeaveTime的
可能包含值'FN'，'AN'或'全天'



如何确认是否已经申请休假？



如果一个人申请半天我如何验证？



样本数据如下

I am storing leaves in a table where the start date and end date are only date part and the time is stored as 'FN' and 'AN' for forenoon and after noon,

for example storing a full day leave is as below

Fromdate : 2016-03-03, fromLeaveTime : 'Full Day'
Todate: 2016-03-03, toLeaveTime:'FN'

fromLeaveTime and toLeaveTime may contain values 'FN', 'AN' or 'Full Day'

how to validate whether a leave is already applied?

if a person apply for half day how can I validate?

sample data is as follows

Id	UserId	FromDate	ToDate		Totaldays		FromLeaveTime	ToLeaveTime
78	118		2016-03-03	2016-03-03	1				Full Day		Full Day
79	118		2016-03-04	2016-03-04	0.5				FN				FN
80	118		2016-03-07	2016-03-07	0.5				AN				AN
81	118		2016-03-08	2016-03-09	1.5				Full Day		FN
82	118		2016-03-10	2016-03-11	1				AN				FN

我的尝试：

What I have tried:

IF EXISTS(SELECT * FROM WorkFromHome WHERE UserId=@Id AND ((@FromDate between FromDate and ToDate) OR (@ToDate between FromDate and ToDate)))
	 BEGIN
	 
	 
	 
	 RAISERROR('Already Applied',16,1);
	 return;
	  END

推荐答案

假设您在数据库中有以下数据）

来自：03-03-2016收件价：04-03-2016

然后你的查询就像是

Suppose You have following data in database)
From :03-03-2016 To:04-03-2016
Then your query will be like

IF EXISTS(SELECT * FROM WorkFromHome WHERE UserId=@Id AND ((@FromDate >= FromDate and ToDate) OR (@ToDate <= FromDate and ToDate)))
BEGIN
	 RAISERROR('Already Applied',16,1);
	 return;
END

因为它可以是fromdate本身，所以介于两者之间是行不通的。



如果你有进一步的询问，请告诉我。

As it can be the fromdate itself, so between won't work.

Let me know if you have further query.

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