--> 生成测试数据: @record DECLARE @record TABLE (CardID VARCHAR(3),first DATETIME,second DATETIME) INSERT INTO @record SELECT '001','2009-05-06 09:10:00','2009-05-06 19:12:00' UNION ALL SELECT '002','2009-05-06 09:20:00',null UNION ALL SELECT '003','2009-05-06 09:15:00','2009-05-06 18:50:00'
--> 生成测试数据: @arrage DECLARE @arrage TABLE (CardID VARCHAR(3),font1 DATETIME,now1 DATETIME,later1 DATETIME,font2 DATETIME,now2 DATETIME,later2 DATETIME) INSERT INTO @arrage SELECT '001','08:45:00','09:00:00','09:15:00','18:45:00','19:00:00','19:15:00' UNION ALL SELECT '002','08:45:00','09:00:00','09:15:00','18:45:00','19:00:00','19:15:00' UNION ALL SELECT '003','08:45:00','09:00:00','09:15:00','18:45:00','19:00:00','19:15:00'--SQL查询如下:SELECT A.CardID, CASE WHEN CONVERT(VARCHAR(10),A.first,108) BETWEEN B.font1 AND later1 THEN CONVERT(VARCHAR(10),A.first,120)+' ' +CONVERT(VARCHAR(10),B.now1,108) ELSE A.first END AS first, CASE WHEN CONVERT(VARCHAR(10),A.second,108) BETWEEN B.font2 AND later2 THEN CONVERT(VARCHAR(10),A.second,120)+' ' +CONVERT(VARCHAR(10),B.now2,108) ELSE A.second END AS second FROM @record AS A LEFT JOIN @arrage AS B ON A.CardID=B.CardID/* CardID first second ------ ----------------------- ----------------------- 001 2009-05-06 09:00:00.000 2009-05-06 19:00:00.000 002 2009-05-06 09:20:00.000 NULL 003 2009-05-06 09:00:00.000 2009-05-06 19:00:00.000(3 行受影响) */
update a set first = case when right(convert(char(19),first,120),8) between font1 and now1 then cast( convert(char(10),first,120)+' '+now1 as datetime) else first end, second= case when right(convert(char(19),second,120),8) between font2and now2 then cast( convert(char(10),second,120)+' '+now2 as datetime) else second endfrom record a ,arrage b where a.cardid = b..cardid
-- Author: liangCK 小梁
-- Date : 2009-05-07 15:32:10
---------------------------------
--> 生成测试数据: @record
DECLARE @record TABLE (CardID VARCHAR(3),first DATETIME,second DATETIME)
INSERT INTO @record
SELECT '001','2009-05-06 09:10:00','2009-05-06 19:12:00' UNION ALL
SELECT '002','2009-05-06 09:20:00',null UNION ALL
SELECT '003','2009-05-06 09:15:00','2009-05-06 18:50:00'
--> 生成测试数据: @arrage
DECLARE @arrage TABLE (CardID VARCHAR(3),font1 DATETIME,now1 DATETIME,later1 DATETIME,font2 DATETIME,now2 DATETIME,later2 DATETIME)
INSERT INTO @arrage
SELECT '001','08:45:00','09:00:00','09:15:00','18:45:00','19:00:00','19:15:00' UNION ALL
SELECT '002','08:45:00','09:00:00','09:15:00','18:45:00','19:00:00','19:15:00' UNION ALL
SELECT '003','08:45:00','09:00:00','09:15:00','18:45:00','19:00:00','19:15:00'--SQL查询如下:SELECT
A.CardID,
CASE WHEN CONVERT(VARCHAR(10),A.first,108)
BETWEEN B.font1 AND later1
THEN CONVERT(VARCHAR(10),A.first,120)+' '
+CONVERT(VARCHAR(10),B.now1,108)
ELSE A.first END AS first,
CASE WHEN CONVERT(VARCHAR(10),A.second,108)
BETWEEN B.font2 AND later2
THEN CONVERT(VARCHAR(10),A.second,120)+' '
+CONVERT(VARCHAR(10),B.now2,108)
ELSE A.second END AS second
FROM @record AS A
LEFT JOIN @arrage AS B
ON A.CardID=B.CardID/*
CardID first second
------ ----------------------- -----------------------
001 2009-05-06 09:00:00.000 2009-05-06 19:00:00.000
002 2009-05-06 09:20:00.000 NULL
003 2009-05-06 09:00:00.000 2009-05-06 19:00:00.000(3 行受影响)
*/
set first = case when right(convert(char(19),first,120),8) between font1 and now1 then cast( convert(char(10),first,120)+' '+now1 as datetime) else first end,
second= case when right(convert(char(19),second,120),8) between font2and now2 then cast( convert(char(10),second,120)+' '+now2 as datetime) else second endfrom record a ,arrage b
where a.cardid = b..cardid