日期范围之间存在日期 [英] Date Exists between Range of Dates

问题描述

IF（@user_leave_type = 3）

BEGIN

IF EXISTS（SELECT 1 FROM [dbo]。[User_Leave] WHERE CONVERT（DATETIME，[start_date]，101 ）BETWEEN CONVERT（DATETIME，@ start_date，101）AND CONVERT（DATETIME，@ end_date，101）AND

CONVERT（DATETIME，[end_date]，101）BETWEEN CONVERT（DATETIME，@ start_date，101） AND CONVERT（DATETIME，@ end_date，101）和[user_id] = @user_id）

BEGIN

从user_leave中选择@ leave_status = leave_status，其中user_id = @ user_id和CONVERT（ DATETIME，[start_date]，101）= CONVERT（DATETIME，@ start_date，101）AND CONVERT（DATETIME，[end_date]，101）= CONVERT（DATETIME，@ end_date，101）

If（@ leave_status = 1）

开始

SET @active_rec_in = 0 - 离开活动



print' @ active_rec_in 3'

INSERT进入User_Leave

（

[ref_cat_id]，

[user_id]，

[leave_hour]，

[start_date]，

[end_date]，

[leave_desc]，

[active_rec_in]，

[user_leave_type]，
[shift_during_leave]，

[shift_after_leave]，

[working_days_in_shift]，

[task_delegated_to]

）

VALUES

（

@ref_cat_id，

@user_id，

@leave_hour，

@start_date，

@end_date，

@leave_desc，

@active_rec_in，
@user_leave_type，

@shift_during_leave，

@shift_after_leave，

@worked_days_in_shift，

@ task_delegated_to

）

end

IF (@user_leave_type = 3)
BEGIN
IF EXISTS( SELECT 1 FROM [dbo].[User_Leave] WHERE CONVERT(DATETIME,[start_date],101) BETWEEN CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,@end_date,101) AND
CONVERT(DATETIME,[end_date],101) BETWEEN CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,@end_date,101) AND [user_id] = @user_id)
BEGIN
select @leave_status=leave_status from user_leave where user_id=@user_id and CONVERT(DATETIME,[start_date],101)= CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,[end_date],101) = CONVERT(DATETIME,@end_date,101)
If(@leave_status=1)
begin
SET @active_rec_in = 0 -- Leave Active

print '@active_rec_in 3'
INSERT INTO User_Leave
(
[ref_cat_id],
[user_id],
[leave_hour],
[start_date],
[end_date],
[leave_desc],
[active_rec_in],
[user_leave_type],
[shift_during_leave],
[shift_after_leave],
[worked_days_in_shift],
[task_delegated_to]
)
VALUES
(
@ref_cat_id,
@user_id,
@leave_hour,
@start_date,
@end_date,
@leave_desc,
@active_rec_in,
@user_leave_type,
@shift_during_leave,
@shift_after_leave,
@worked_days_in_shift,
@task_delegated_to
)
end

推荐答案

IF (@user_leave_type = 3)
BEGIN
IF EXISTS( SELECT 1 FROM [dbo].[User_Leave] WHERE CONVERT(DATETIME,[start_date],101) BETWEEN CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,@end_date,101) AND
CONVERT(DATETIME,[end_date],101) BETWEEN CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,@end_date,101) AND [user_id] = @user_id)
BEGIN
select @leave_status=leave_status from user_leave where user_id=@user_id and CONVERT(DATETIME,[start_date],101)= CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,[end_date],101) = CONVERT(DATETIME,@end_date,101)
If(@leave_status=1)
begin
SET @active_rec_in = 0 -- Leave Active

print '@active_rec_in 3'
INSERT INTO User_Leave
(
[ref_cat_id],
[user_id],
[leave_hour],
[start_date],
[end_date],
[leave_desc],
[active_rec_in],
[user_leave_type],
[shift_during_leave],
[shift_after_leave],
[worked_days_in_shift],
[task_delegated_to]
)
SELECT
@ref_cat_id,
@user_id,
@leave_hour,
@start_date,
@end_date,
@leave_desc,
@active_rec_in,
@user_leave_type,
@shift_during_leave,
@shift_after_leave,
@worked_days_in_shift,
@task_delegated_to

end
else
SELECT @message=[ref_name] FROM [dbo].[Reference_Categories] WITH (NOLOCK) WHERE [ref_type] = 'MESSAGE' AND [ref_cat_code]=1024

end

ELSE
IF NOT EXISTS( SELECT 1 FROM [dbo].[User_Leave] WHERE CONVERT(DATETIME,[start_date],101) BETWEEN CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,@end_date,101) AND
CONVERT(DATETIME,[end_date],101) BETWEEN CONVERT(DATETIME,@start_date,101) AND CONVERT(DATETIME,@end_date,101) AND [user_id] = @user_id )

BEGIN
SET @active_rec_in = 0 -- Leave Active

print '@active_rec_in 3'
INSERT INTO User_Leave
(
[ref_cat_id],
[user_id],
[leave_hour],
[start_date],
[end_date],
[leave_desc],
[active_rec_in],
[user_leave_type],
[shift_during_leave],
[shift_after_leave],
[worked_days_in_shift],
[task_delegated_to]
)

SELECT
@ref_cat_id,
@user_id,
@leave_hour,
@start_date,
@end_date,
@leave_desc,
@active_rec_in,
@user_leave_type,
@shift_during_leave,
@shift_after_leave,
@worked_days_in_shift,
@task_delegated_to

END
ELSE

IF NOT EXISTS( SELECT 1 FROM [dbo].[User_Leave] WHERE CONVERT(DATETIME,[start_date],101) = CONVERT(DATETIME,@start_date,101) AND [user_id] = @user_id OR CONVERT(DATETIME,[end_date],101) = CONVERT(DATETIME,@end_date,101))
BEGIN
SET @active_rec_in = 0 -- Leave Active

--print '@active_rec_in 3'
INSERT INTO User_Leave
(
[ref_cat_id],
[user_id],
[leave_hour],
[start_date],
[end_date],
[leave_desc],
[active_rec_in],
[user_leave_type],
[shift_during_leave],
[shift_after_leave],
[worked_days_in_shift],
[task_delegated_to]
)
SELECT
@ref_cat_id,
@user_id,
@leave_hour,
@start_date,
@end_date,
@leave_desc,
@active_rec_in,
@user_leave_type,
@shift_during_leave,
@shift_after_leave,
@worked_days_in_shift,
@task_delegated_to

END
END

这篇关于日期范围之间存在日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！