更新求解

Question

sql   查询

Answer 1

你的第一个记录错了,它应该是12-31 的夜班.
create table TableA(ID int, Station nvarchar(10),endTime datetime,rdLong int , RdDate nvarchar(20))
go
insert into tablea select 1,'3#筒仓','2014-01-01 05:45:52.530',9.00,null
insert into tablea select 2,'3#筒仓','2014-01-02 07:36:15.937',9.00,null
insert into tablea select 3,'1#筒仓','2014-01-03 15:08:18.780',42.00,null
insert into tablea select 4,'1#筒仓','2014-01-04 18:08:08.970',42.00,null
insert into tablea select 5,'1#筒仓','2014-01-05 21:08:04.767',43.00,null
go
update TableA set RdDate=
(case when convert(varchar(5),endTime,114)>'17:30' then convert(varchar(10),endTime,120)+' 夜班' 
when convert(varchar(5),endTime,114)<'07:30' then convert(varchar(10),dateadd(d,-1,endTime),120)+' 夜班'
else convert(varchar(10),endTime,120)+' 日班'
end)
select * from tablea
/*
ID          Station    endTime                 rdLong      RdDate
----------- ---------- ----------------------- ----------- --------------------
1           3#筒仓       2014-01-01 05:45:52.530 9           2013-12-31 夜班
2           3#筒仓       2014-01-02 07:36:15.937 9           2014-01-02 日班
3           1#筒仓       2014-01-03 15:08:18.780 42          2014-01-03 日班
4           1#筒仓       2014-01-04 18:08:08.970 42          2014-01-04 夜班
5           1#筒仓       2014-01-05 21:08:04.767 43          2014-01-05 夜班(5 行受影响)*/
go
drop table tablea

Answer 2

update dbo.Table1 set RdDate=
case when convert(varchar(5),endTime,114)>'07:30' and  convert(varchar(5),endTime,114)<'17:30' 
then convert(varchar(10),endTime,120)+'日班' 
else convert(varchar(10),endTime,120)+'夜班' end