日期范围之间存在日期 [英] 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屋!