执行的sql语句是
insert into ClockRecords(ID, CardID, ClockTime)VALUES( '01','000034','2006-05-09 09:36:05')得到的结果是
Server: Msg 242, Level 16, State 3, Procedure prcProcess_Stud, Line 46
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated.其中ClockTime字段是vchar(50),数据'2006-05-09 09:36:05',就是数据中年月日和时间中间有个空格,有空格就报上面的错误,刚开始以为繁体2000和英文sql2000不支持空格,我把空格替换成0,还是报错,不知道是什么问题,上面的插入语句在简体操作系统下没问题的
insert into ClockRecords(ID, CardID, ClockTime)VALUES( '01','000034','2006-05-09 09:36:05')得到的结果是
Server: Msg 242, Level 16, State 3, Procedure prcProcess_Stud, Line 46
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated.其中ClockTime字段是vchar(50),数据'2006-05-09 09:36:05',就是数据中年月日和时间中间有个空格,有空格就报上面的错误,刚开始以为繁体2000和英文sql2000不支持空格,我把空格替换成0,还是报错,不知道是什么问题,上面的插入语句在简体操作系统下没问题的
@CID varchar(50)
AS
BEGIN
DECLARE @val varchar(50), @varSms varchar(100), @varDate varchar(50)
SET @varDate = GETDATE()
DECLARE stud_cursor CURSOR FOR SELECT a.ClockNumber, d.userid, d.[name], c.CardID, CONVERT(varchar(20), a.ClockTime, 120), a.MachineID, b.TimeOne_Begin, b.TimeOne_End, b.TimeTwo_Begin, b.TimeTwo_End, b.TimeThree_Begin, b.TimeThree_End, b.TimeFour_Begin, b.TimeFour_End, b.TimeRe FROM MK_ClockRecords a JOIN MK_ClockTime b ON a.MachineID = b.MachineID JOIN MK_IssueCard_Records c ON a.CardID = c.CardID JOIN student d ON c.PersonID = d.userid WHERE a.IsProcess = 0 AND c.CardID = @CID ORDER BY a.ClockTime ASC
OPEN stud_cursor
DECLARE @ClockNumber SYSNAME, @userid SYSNAME, @name SYSNAME, @CardID SYSNAME, @ClockTime SYSNAME, @MachineID SYSNAME, @TimeOne_Begin SYSNAME, @TimeOne_End SYSNAME, @TimeTwo_Begin SYSNAME, @TimeTwo_End SYSNAME, @TimeThree_Begin SYSNAME, @TimeThree_End SYSNAME, @TimeFour_Begin SYSNAME, @TimeFour_End SYSNAME, @flag SYSNAME
FETCH NEXT FROM stud_cursor INTO @ClockNumber, @userid, @name, @CardID, @ClockTime, @MachineID, @TimeOne_Begin, @TimeOne_End, @TimeTwo_Begin, @TimeTwo_End, @TimeThree_Begin, @TimeThree_End, @TimeFour_Begin, @TimeFour_End, @flag
WHILE(@@FETCH_STATUS = 0)
BEGIN
DECLARE @T varchar(50), @T1 varchar(50), @T2 varchar(50), @isLate1 varchar(10), @isEarly1 varchar(10)
SET @T = @ClockTime --SUBSTRING(@ClockTime, 12, 5)
SET @T1 = LTRIM(SUBSTRING(@T, 12, 5)) --取得时间--1111
SET @T2 = RTRIM(SUBSTRING(@T, 1, 10)) --取得年月这是存储过程的声明部分
--上午进
IF @T1 >= @TimeOne_Begin AND @T1 <= @TimeOne_End
BEGIN
SET @val = dbo.fnCheckTime(@userid, @T1, 0, 1, 1)
IF @val != ''
BEGIN
SET @isLate1 = SUBSTRING(@val, 3, 1)
SET @isEarly1 = SUBSTRING(@val, 5, 1)
SELECT * FROM timecard_stud WHERE userid = @userid AND CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2 AND intime IS NOT NULL AND am_pm = 1
IF @@ROWCOUNT > 0
--有则更新
UPDATE timecard_stud SET intime = @T, if_late = @isLate1 WHERE userid = @userid AND am_pm = 1 AND intime IS NOT NULL AND CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2
ELSE
--无则插入
EXEC prc_timecard_stud_insert @userid, @T2, 1, @T, '', 0, @isLate1, @isEarly1, 0, 0
END
SET @varSms = @name + '在 ' + @T1 + ' 进入学校'
END
--上午出
ELSE IF @T1 >= @TimeTwo_Begin AND @T1 <= @TimeTwo_End
BEGIN
--判断此人在本日内上午有无出门记录
/*
参数说明:
/// <param name="@UID">编号</param>
/// <param name="@UTime">打卡时间</param>
/// <param name="@isTop">上下午(0-上,1-下)</param>
/// <param name="@isStud">是否为学生(0-职工,1-学生)</param>
/// <param name="@isIn">是否为进(0-出,1-进)</param>
*/
SET @val = dbo.fnCheckTime(@userid, @T1, 0, 1, 0)
IF SUBSTRING(@val, 1, 1) = '2' --二时段处理
BEGIN
SET @isLate1 = SUBSTRING(@val, 3, 1)
SET @isEarly1 = SUBSTRING(@val, 5, 1)
SELECT * FROM timecard_stud WHERE userid = @userid AND CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2 AND outtime IS NOT NULL AND am_pm = 1
IF @@ROWCOUNT > 0
BEGIN
--有则更新
UPDATE timecard_stud SET outtime = @T, if_early = @isEarly1 WHERE userid = @userid AND am_pm = 1 AND outtime IS NOT NULL AND CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2
END
ELSE BEGIN
--无则插入
EXEC prc_timecard_stud_insert @userid, @T2, 1, '', @T, 0, @isLate1, @isEarly1, 0, 0
END
END
ELSE
BEGIN
SET @isLate1 = SUBSTRING(@val, 3, 1)
SET @isEarly1 = SUBSTRING(@val, 5, 1)
SELECT * FROM timecard_stud WHERE userid = @userid AND CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2 AND intime IS NOT NULL AND am_pm = 1
IF @@ROWCOUNT > 0
BEGIN
--有则更新
UPDATE timecard_stud SET outtime = @T, if_early = @isEarly1 WHERE userid = @userid AND am_pm = 1 AND intime IS NOT NULL AND CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2;
END
ELSE
BEGIN
--无则插入
EXEC prc_timecard_stud_insert @userid, @T2 , 1, '', @T, 0, @isLate1, @isEarly1, 0, 0
END
END
SET @varSms = @name + '在 ' + @T1 + ' 离开'
END
BEGIN
--判断此人在本日内下午有无进门记录
/*
参数说明:
/// <param name="@UID">编号</param>
/// <param name="@UTime">打卡时间</param>
/// <param name="@isTop">上下午(0-上,1-下)</param>
/// <param name="@isStud">是否为学生(0-职工,1-学生)</param>
/// <param name="@isIn">是否为进(0-出,1-进)</param>
*/
SET @val = dbo.fnCheckTime(@userid, @T1, 1, 1, 1)
IF SUBSTRING(@val, 1, 1) = '2' --二时段处理
BEGIN
SET @isLate1 = SUBSTRING(@val, 3, 1)
SET @isEarly1 = SUBSTRING(@val, 5, 1)
SELECT * FROM timecard_stud WHERE userid = @userid AND CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2 AND intime IS NOT NULL AND am_pm = 2
IF @@ROWCOUNT > 0
BEGIN
--有则更新
UPDATE timecard_stud SET intime = @T, if_late = @isLate1 WHERE userid = @userid AND am_pm = 2 AND intime IS NOT NULL AND CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2
END
ELSE
BEGIN
--无则插入
EXEC prc_timecard_stud_insert @userid, @T2, 2, @T, '', 0, @isLate1, @isEarly1, 0, 0
END
END
ELSE
BEGIN
SET @isLate1 = SUBSTRING(@val, 3, 1)
SET @isEarly1 = SUBSTRING(@val, 5, 1)
SELECT * FROM timecard_stud WHERE userid = @userid AND CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2 AND intime IS NOT NULL AND am_pm = 1
IF @@ROWCOUNT > 0
BEGIN
--有则更新
UPDATE timecard_stud SET intime = @T, if_late = @isLate1 WHERE userid = @userid AND am_pm = 1 AND intime IS NOT NULL AND CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2
END
ELSE
BEGIN
--无则插入
EXEC prc_timecard_stud_insert @userid, @T2, 1, @T, '', 0, @isLate1, @isEarly1, 0, 0
END
END
SET @varSms = @name + '在 ' + @T1 + ' 进入'
END
UPDATE timecard_stud SET intime = @T, if_late = @isLate1 WHERE userid = @userid AND am_pm = 1 AND intime IS NOT NULL AND CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2
和类似的语句
CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate), 1, 10)) = @T2
--〉
CONVERT(DATETIME, SUBSTRING(CONVERT(varchar(10), trxdate,120), 1, 10)) = @T2
写的是够乱的,好多方法有问题