关于SQL server 2008 存储过程里编写数据插入之前的时间条件问题,急急急!!! 40C

如题,现在我想给数据插入此table5表格之前,做一个时间段的条件,例如,今天是星期三,星期三的早上8点到晚上8点这个时间里,数据就往指定的字段插入做统计(数据插入和统计的代码已经编写好了,就是条件出了问题),然后晚上8点到第二天(星期四),早上8点,数据就往另一个指定字段插入,现在我写好了时间条件,但出了问题,就是如果当天的早8点到晚8点的话,数据的确是能插入到指定的字段,但如果碰到是跨天的时间,例如就是星期三的晚上8点跨到第二天星期四的早上8点这种情况的时候,数据就不会插入了。。。。。。我不知道是什么问题,本人实习生,小白一枚,求SQL server大神和专家急救!!非常急!!!!
加Q联系我:584958184

附上我的时间条件代码:
图片说明

图片说明

7个回答

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
wodeqq584958184 感谢
一年多之前 回复
wodeqq584958184
wodeqq584958184 回复fffssso: 问题我自己解决了
一年多之前 回复
fffssso
Zhang15150360236 回复fffssso: 感觉你这代码主要是判断语句中少了括号吧,把周三的都扩一起,周四的扩一起,再用or连接起来
一年多之前 回复
fffssso
Zhang15150360236 判断如果当前时间是周四,set @s=datename(hh,getdate())+24,后面的判断语句周四那个改为8+24就好了
一年多之前 回复
wodeqq584958184
wodeqq584958184 回复zfhuaq: windows计划任务???什么鬼...能详细解释下吗
一年多之前 回复
zfhuaq
zfhuaq windows计划任务
一年多之前 回复
wodeqq584958184
wodeqq584958184 看不懂。。。。。 找不出跟我这个有什么联系
一年多之前 回复

有SQL server大神前来解救吗~!! 非常急啊~!!!急救!!!

你把代码贴出来啊,都不知道你是怎么加的时间条件

wodeqq584958184
wodeqq584958184 已经贴出来了
一年多之前 回复
wodeqq584958184
wodeqq584958184 稍等 我整个存储过程贴出来
一年多之前 回复

这是我完整的存储过程代码

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

提示:我的这个存储过程的执行是通过另一个的触发器触发后执行,触发条件是当table4有数据进来时,满足条件后就执行这个存储过程

 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

qq_35955916
程序员的键盘 根据自己的 准确需求来调整范围界定(等号的位置)
一年多之前 回复

时间判断有点问题,星期三和星期四的限制条件用括号分开,中间用OR判断

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!

相似问题

4
关于SQL server 2008如何求每一行几列的数据总计并赋值到另一个字段中
4
关于SQL server 2008时间差datediff函数计算问题
7
关于SQL server 2008 触发器 的问题
2
sql server 2008存储过程向变量赋值
2
SQL server 2008R2配置服务器下SQL server服务远程过程调用失败
1
安装SQL Server2017时安装到数据库引擎、SQL客户端连接、SQL客户端连接SDK报错
5
传入数据类型是List<String> 数据库的字段类型应该是什么 SQL server
0
C++如何通过ADO传递表值参数到SQL Sever存储过程?-- 【已解决】
1
Sql Server中关于CDC(数据更改捕获)的问题:sys.sp_MScdc_capture_job中找不到相应依赖存储过程与函数
2
sql server新增数据时主键自动生成
3
sql server无法连接服务器,sql server网络配置里面没有任何协议
0
sql server使用sp_addlinkedserver连接另一台数据库时,所做的查询操作是由哪台服务器承当运算压力的.
2
Sql Server Management Studio如何连接到VisualStudio2017自带的数据库?
0
sql创建存储过程的问题
1
求助:关于数据库中如何存储一段视频时长的问题?
1
如何用C# winform读取存储在SQL数据表中的excel文件路径,然后将excel文件中的数据以折线图显示出来?
0
在使用matlab对SQL SERVER进行数据更新操作时,显示我的列名无效
3
sql server数据库,现在我有一张表的数据,分别放在两个数据库上。
1
求助,怎么用存储过程导出一个库所有的表数据