594
社区成员
发帖
与我相关
我的任务
分享
DECLARE @StartD DATETIME= '2017-01-02 09:02:21.287' ,
@EndD DATETIME= '2017-01-03 19:00:21.287' ,
@dd INT ,
@hh INT ,
@StartT DATETIME= '08:00' ,
@EndT DATETIME= '20:00';
SELECT @dd = DATEDIFF(dd, @StartD, @EndD) ,
@hh = DATEPART(hh, @EndT) - DATEPART(hh, @StartT),
@hh = @hh * ( @dd + 1 )
+ CASE WHEN @StartD > DATEADD(hh, DATEPART(hh, @StartT),
CONVERT(VARCHAR(10), @StartD, 120))
THEN DATEDIFF(hh, @StartD,
DATEADD(hh, DATEPART(hh, @StartT),
CONVERT(VARCHAR(10), @StartD, 120)))
ELSE 0
END
+ CASE WHEN @EndD < DATEADD(hh, DATEPART(hh, @EndT),
CONVERT(VARCHAR(10), @EndD, 120))
THEN CASE WHEN @StartD < DATEADD(hh, DATEPART(hh, @EndT),
CONVERT(VARCHAR(10), @EndD, 120))
THEN DATEDIFF(hh,
DATEADD(hh, DATEPART(hh, @EndT),
CONVERT(VARCHAR(10), @EndD, 120)),
@EndD)
ELSE -@hh
END
ELSE 0
END;
SELECT @hh;
/*
22
*/
DECLARE @StartD DATETIME= '2017-01-02 02:02:21.287' ,
@EndD DATETIME= '2017-01-05 06:00:21.287' ,
@dd INT ,
@hh INT ,
@StartT DATETIME= '08:00' ,
@EndT DATETIME= '20:00';
SELECT @dd = DATEDIFF(dd, @StartD, @EndD) ,
@hh = DATEPART(hh, @EndT) - DATEPART(hh, @StartT) ,
@hh = @hh * ( @dd + 1 )
+ CASE WHEN @StartD > DATEADD(hh, DATEPART(hh, @StartT),
CONVERT(VARCHAR(10), @StartD, 120))
THEN DATEDIFF(hh, @StartD,
DATEADD(hh, @hh,
CONVERT(VARCHAR(10), @StartD, 120)))
ELSE 0
END
+ CASE WHEN @EndD < DATEADD(hh, DATEPART(hh, @EndT),
CONVERT(VARCHAR(10), @EndD, 120))
THEN CASE WHEN @StartD > DATEADD(hh, DATEPART(hh, @EndT),
CONVERT(VARCHAR(10), @EndD, 120))
THEN DATEDIFF(hh,
DATEADD(hh, DATEPART(hh, @EndT),
CONVERT(VARCHAR(10), @EndD, 120)),
@EndD)
ELSE -@hh
END
ELSE 0
END;
SELECT @hh;
/*
36
*/
DECLARE @StartD DATETIME= '2017-01-02 02:02:21.287' ,
@EndD DATETIME= '2017-01-05 08:00:21.287' ,
@dd INT ,
@hh INT ,
@StartT DATETIME= '08:00' ,
@EndT DATETIME= '20:00';
SELECT @dd = DATEDIFF(dd, @StartD, @EndD) ,
@hh = DATEPART(hh, @EndT) - DATEPART(hh, @StartT) ,
@hh = @hh * ( @dd + 1 )
+ CASE WHEN @StartD > DATEADD(hh, DATEPART(hh, @StartT),
CONVERT(VARCHAR(10), @StartD, 120))
THEN DATEDIFF(hh, @StartD,
DATEADD(hh, @hh,
CONVERT(VARCHAR(10), @StartD, 120)))
ELSE 0
END
+ CASE WHEN @EndD < DATEADD(hh, DATEPART(hh, @EndT),
CONVERT(VARCHAR(10), @EndD, 120))
THEN DATEDIFF(hh,
DATEADD(hh, DATEPART(hh, @EndT),
CONVERT(VARCHAR(10), @EndD, 120)), @EndD)
ELSE 0
END;
SELECT @hh;
/*
36
*/