- 关于SQL server 2008 存储过程里编写数据插入之前的时间条件问题，急急急！！！ 40C
-
如题，现在我想给数据插入此table5表格之前，做一个时间段的条件，例如，今天是星期三，星期三的早上8点到晚上8点这个时间里，数据就往指定的字段插入做统计（数据插入和统计的代码已经编写好了，就是条件出了问题），然后晚上8点到第二天（星期四），早上8点，数据就往另一个指定字段插入，现在我写好了时间条件，但出了问题，就是如果当天的早8点到晚8点的话，数据的确是能插入到指定的字段，但如果碰到是跨天的时间，例如就是星期三的晚上8点跨到第二天星期四的早上8点这种情况的时候，数据就不会插入了。。。。。。我不知道是什么问题，本人实习生，小白一枚，求SQL server大神和专家急救！！非常急！！！！
加Q联系我：584958184附上我的时间条件代码:
IF OBJECT_ID ('dbo.varbin2hexstr') IS NOT NULL
DROP FUNCTION dbo.varbin2hexstr
GO
CREATE function varbin2hexstr(
@bin varbinary(8000)
)returns varchar(8000)
as
begin
declare @re varchar(8000),@i int
select @re='',@i=datalength(@bin)
while @i>0
select @re=substring('0123456789ABCDEF',substring(@bin,@i,1)/16+1,1)
+substring('0123456789ABCDEF',substring(@bin,@i,1)%16+1,1)
+@re
,@i=@i-1
-- return('0x'+@re)
return @re
end
GO
IF OBJECT_ID ('dbo.varbin2hexstr') IS NOT NULL
DROP FUNCTION dbo.varbin2hexstr
GO
CREATE function varbin2hexstr(
@bin varbinary(8000)
)returns varchar(8000)
as
begin
declare @re varchar(8000),@i int
select @re='',@i=datalength(@bin)
while @i>0
select @re=substring('0123456789ABCDEF',substring(@bin,@i,1)/16+1,1)
+substring('0123456789ABCDEF',substring(@bin,@i,1)%16+1,1)
+@re
,@i=@i-1
-- return('0x'+@re)
return @re
end
GO
-
- wodeqq584958184 感谢
- 大约 2 年之前 回复
-
- wodeqq584958184 回复fffssso: 问题我自己解决了
- 大约 2 年之前 回复
-
- Zhang15150360236 回复fffssso: 感觉你这代码主要是判断语句中少了括号吧，把周三的都扩一起，周四的扩一起，再用or连接起来
- 大约 2 年之前 回复
-
- Zhang15150360236 判断如果当前时间是周四，set @s=datename(hh,getdate())+24，后面的判断语句周四那个改为8+24就好了
- 大约 2 年之前 回复
-
- wodeqq584958184 回复zfhuaq: windows计划任务？？？什么鬼...能详细解释下吗
- 大约 2 年之前 回复
-
- wodeqq584958184 看不懂。。。。。 找不出跟我这个有什么联系
- 大约 2 年之前 回复
-
- wodeqq584958184 已经贴出来了
- 大约 2 年之前 回复
-
- wodeqq584958184 稍等 我整个存储过程贴出来
- 大约 2 年之前 回复
这是我完整的存储过程代码
USE [plc]
GO
/****** Object: StoredProcedure [dbo].[sp_table5_insert] Script Date: 05/30/2018 08:54:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 插入T5逻辑实现
ALTER proc [dbo].[sp_table5_insert]
@_T4ID BIGINT -- machine-status=0的自增id
as
BEGIN
-- 上一条错误代码记录id
DECLARE @_T4ErrorCodeID BIGINT
-- 分钟时差
DECLARE @_timeDifferenceMin BIGINT
--时间段变量
DECLARE @shi int
set @shi=Datename(HOUR,GetDate())
--星期段变量
DECLARE @xingqi nvarchar(255)
set datefirst 1
set @xingqi=Datename(WEEKDAY,GETDATE())
-- 开始时间
DECLARE @_machineItime VARCHAR(50)
-- 获取上一条错误代码记录id并且计算以分钟为单位的时差
SELECT TOP 1 @_T4ErrorCodeID = id, @_timeDifferenceMin = DATEDIFF(S,ng_itime,(SELECT machine_itime FROM table4
WHERE id = @_T4ID))
FROM table4
WHERE id < @_T4ID AND location IS NOT NULL AND pointer IS NOT NULL AND ng_itime IS NOT NULL
ORDER BY id DESC;
--判断时间，星期一早8点到晚8点就把数据插入到指定字段中
if(@xingqi='星期一') and (@shi>8 and @shi<20)
begin
-- 对T5表操作，存在类型累加时间，不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
-- 修改
UPDATE table5
SET [Montime_8:00AM-8:00PM] = ISNULL([Montime_8:00AM-8:00PM],0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
-- 插入
INSERT INTO table5(location,pointer,[Montime_8:00AM-8:00PM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
end
--判断时间，星期一晚8点到星期二早8点
if(@xingqi='星期一' and @shi>20) or (@xingqi='星期二' and @shi<8)
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Montime_8:00PM-8:00AM] = ISNULL([Montime_8:00PM-8:00AM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Montime_8:00PM-8:00AM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期二早8点到星期二晚8点
if(@xingqi='星期二' ) and (@shi>8 and @shi<8 )
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Tuetime_8:00AM-8:00PM] = ISNULL([Tuetime_8:00AM-8:00PM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Tuetime_8:00AM-8:00PM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期二晚8点到星期三早8点
if(@xingqi='星期二' and @shi>20 ) or (@xingqi='星期三' and @shi<8 )
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Tuetime_8:00PM-8:00AM] = ISNULL([Tuetime_8:00PM-8:00AM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Tuetime_8:00PM-8:00AM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期三早8点到星期三晚8点
set datefirst 1
set @xingqi=Datename(WEEKDAY,GETDATE())
if(@xingqi='星期三') and (@shi>8 and @shi<20)
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Wedtime_8:00AM-8:00PM] = ISNULL([Wedtime_8:00AM-8:00PM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Wedtime_8:00AM-8:00PM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期三晚8点到星期四早8点
set datefirst 1
set @xingqi=Datename(WEEKDAY,GETDATE())
if(@xingqi='星期三' and @shi>20 or @xingqi='星期四' and @shi<8)
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Wedtime_8:00PM-8:00AM] = ISNULL([Wedtime_8:00PM-8:00AM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Wedtime_8:00PM-8:00AM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期四早8点到星期四晚8点
if(@xingqi='星期四') and (@shi>8 and @shi<20)
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5q
SET [Thutime_8:00AM-8:00PM]= ISNULL([Thutime_8:00AM-8:00PM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Thutime_8:00AM-8:00PM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期四晚8点到星期五早8点
if(@xingqi='星期四' and @shi>20 ) or (@xingqi='星期五' and @shi<8)
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Thutime_8:00PM-8:00AM] = ISNULL([Thutime_8:00PM-8:00AM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Thutime_8:00PM-8:00AM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期五早8点到星期五晚8点
if(@xingqi='星期五') and (@shi>8 and @shi<20)
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Fritime_8:00AM-8:00PM] = ISNULL([Fritime_8:00AM-8:00PM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Fritime_8:00AM-8:00PM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期五晚8点到星期六早8点
if(@xingqi='星期五' and @shi>20) or (@xingqi='星期六' and @shi<8)
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Fritime_8:00PM-8:00AM] = ISNULL([Fritime_8:00PM-8:00AM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Fritime_8:00PM-8:00AM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期六早8点到星期六晚8点
if(@xingqi='星期六' ) and (@shi>8 and @shi<20)
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Sattime_8:00AM-8:00PM] = ISNULL([Sattime_8:00AM-8:00PM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Sattime_8:00AM-8:00PM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期六晚8点到星期天早8点
if(@xingqi='星期六' and @shi>20 ) or (@xingqi='星期天' and @shi<8)
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Sattime_8:00PM-8:00AM] = ISNULL([Sattime_8:00PM-8:00AM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Sattime_8:00PM-8:00AM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期天早8点到星期天晚8点
if(@xingqi='星期天' ) and (@shi>8 and @shi<20)
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Suntime_8:00AM-8:00PM] = ISNULL([Suntime_8:00AM-8:00PM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Suntime_8:00AM-8:00PM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
--判断时间，星期天晚8点星期一早8点
if(@xingqi='星期天' and @shi>20 ) or (@xingqi='星期一' and @shi<8)
begin
--对T5表操作存在类型累加时间不存在新增
IF EXISTS (SELECT * FROM table5 AS t5 LEFT JOIN table4 AS t4 ON t4.location=t5.location AND t4.pointer=t5.pointer
WHERE t4.id=@_T4ErrorCodeID)
--修改
UPDATE table5
SET [Suntime_8:00PM-8:00AM] = ISNULL([Suntime_8:00PM-8:00AM] ,0) + @_timeDifferenceMin
FROM table5 AS t5, table4 AS t4
WHERE t4.location=t5.location AND t4.pointer=t5.pointer AND t4.id=@_T4ErrorCodeID;
ELSE
--插入
INSERT INTO table5(location,pointer,[Suntime_8:00PM-8:00AM])
SELECT location,pointer,@_timeDifferenceMin
FROM table4
WHERE id = @_T4ErrorCodeID;
END
END
declare @week int
declare @hour int
SELECT @week=DatePart(Dw, GETDATE()) --周日为1，周六为7
SELECT @hour=DatePart(Hh, GETDATE()) --24小时
--星期三 早上8点到晚上8点
if(@week=4 and 8<= @hour and @hour<=20)
begin
select 1
end
--星期三 晚上9点到星期四早上7点
if((@week=4 and @hour>20) or (@week=5 and @hour<8))
begin
select 2
end
- 博客 大学四年自学走来，这些私藏的实用工具/学习网站我贡献出来了
- 博客 在中国程序员是青春饭吗？
- 博客 springboot+jwt实现token登陆权限认证
- 博客 技术大佬：我去，你写的 switch 语句也太老土了吧
- 博客 女程序员，为什么比男程序员少？？？
- 博客 总结了 150 余个神奇网站，你不来瞅瞅吗？
- 博客 副业收入是我做程序媛的3倍，工作外的B面人生是怎样的？
- 博客 MySQL数据库面试题（2020最新版）
- 博客 如果你是老板，你会不会踢了这样的员工？
- 博客 我入职阿里后，才知道原来简历这么写
- 博客 程序员写出这样的代码，能不挨骂吗？
- 博客 外包程序员的幸福生活
- 博客 优雅的替换if-else语句
- 博客 离职半年了，老东家又发 offer，回不回？
- 博客 2020阿里全球数学大赛：3万名高手、4道题、2天2夜未交卷
- 博客 为什么你不想学习？只想玩？人是如何一步一步废掉的
- 博客 男生更看重女生的身材脸蛋，还是思想？
- 博客 为什么程序员做外包会被瞧不起？
- 博客 当HR压你价，说你只值7K，你该怎么回答？
- 博客 面试：第十六章：Java中级开发
- 博客 早上躺尸，晚上干活：硅谷科技公司这么流行迟到？
- 博客 面试阿里p7，被按在地上摩擦，鬼知道我经历了什么？
- 博客 终于懂了TCP和UDP协议区别
- 博客 Python爬虫，高清美图我全都要（彼岸桌面壁纸）
- 博客 无代码时代来临，程序员如何保住饭碗？
- 博客 面试了一个 31 岁程序员，让我有所触动，30岁以上的程序员该何去何从？
- 博客 大三实习生，字节跳动面经分享，已拿Offer
- 博客 程序员垃圾简历长什么样？
- 博客 Java岗开发3年，公司临时抽查算法，离职后这几题我记一辈子
- 博客 大胆预测下未来5年的Web开发