SQL转换时间的时分 

1 SELECT     WorkerNo, DutyTime, DATENAME(weekday, DutyTime) AS WeekDay, CycleType, CycleNumber, YnOnDuty, 
2                       CASE WHEN YnOnDuty = 0 THEN '休息' ELSE RIGHT('0' + CAST((CASE WHEN (OneOnDuty - OneOnDuty % 60) 
3                       / 60 < 24 THEN (OneOnDuty - OneOnDuty % 60) / 60 ELSE (OneOnDuty - OneOnDuty % 60) / 60 - 24 END) AS varchar(2)), 2) 
4                       + ':' + RIGHT('0' + CAST(OneOnDuty % 60 AS varchar(2)), 2) END AS OneOnduty, 
5                       CASE WHEN YnOnDuty = 0 THEN '休息' ELSE RIGHT('0' + CAST((CASE WHEN (OneOffDuty - OneOffDuty % 60) 
6                       / 60 < 24 THEN (OneOffDuty - OneOffDuty % 60) / 60 ELSE (OneOffDuty - OneOffDuty % 60) / 60 - 24 END) AS varchar(2)), 2) 
7                       + ':' + RIGHT('0' + CAST(OneOffDuty % 60 AS varchar(2)), 2) END AS OneOffduty, 
Creationtime, CreationName, EditTime, EditName, 
8 StartUsingDate, Description, ID, (CASE WHEN datediff(day, DutyTime, getdate()) > 0 THEN 1 ELSE 0 END) AS Yn
9 FROM         dbo.T_OnDutyOrder

 

posted @ 2016-11-03 15:20  温故余学  阅读(1808)  评论(0)  编辑  收藏  举报