现在有表nowtime,有4个字段,time1(datetime),time2(datetime),time3(datetime),time4(datetime)
time1,time2已经有数据
time1 time2 time3 time4
1900-01-01 07:01:37.000 1900-01-01 19:01:37.000
1900-01-01 07:16:74.000 1900-01-01 19:42:37.000
1900-01-01 07:42:45.000 1900-01-01 19:35:37.000
1900-01-01 07:56:35.000 1900-01-01 20:14:37.000
1900-01-01 07:21:37.000 1900-01-01 20:03:23.000
1900-01-01 07:32:37.000 1900-01-01 21:25:56.000
1900-01-01 08:43:37.000 1900-01-01 22:45:34.000
1900-01-01 11:34:37.000 1900-01-01 23:47:78.000
1900-01-01 12:45:37.000 1900-01-01 20:55:23.000
1900-01-01 14:14:37.000 1900-01-01 23:58:12.000
1900-01-01 15:16:74.000 1900-01-01 23:42:23.000 如何编写SQL语句、使用datepart(minute,time1)获取time1的分钟、
如果 time1的分钟在 0-14之间,那么time3的时间就是time1的小时数和固定值'15'分钟组成的时间
datepart(minute,time1) BETWEEN 0 AND 14
如果 time1的分钟在15-44之间,那么time3的时间就是time1的小时数和固定值'45'分钟组成的时间
datepart(minute,time1) BETWEEN 15 AND 44
如果 tim1的分钟在45以上,那么time3的时间就是(time1小时数+1)和固定值'15'分钟组成的时间
datepart(minute,time1) BETWEEN 45 AND 60用datepart(minute,time2)获取time2的分钟、
如果time2的分钟在0-14之间,那么time4的时间就是(time2的小时数-1)和固定值'45'分钟组成的时间
datepart(minute,time2) between 0 and 14
如果time2的分钟在15-44之间,那么time4的时间就是time2的小时数和固定值'15'分钟组成的时间
datepart(minute,time2) between 15-44
如果time2的分钟在45以上,那么time4的时间就是time2的小时数和固定值'45'分钟组成的时间
datepart(minute,time2) between 45-60
time1 time2 time3 time4
1900-01-01 07:01:37.000 1900-01-01 19:01:37.000 1900-01-01 07:15:00.000 1900-01-01 18:45:00.000
1900-01-01 07:16:74.000 1900-01-01 19:42:37.000 1900-01-01 07:45:00.000 1900-01-01 19:15:00.000
1900-01-01 07:42:45.000 1900-01-01 19:35:37.000 1900-01-01 07:45:00.000 1900-01-01 19:15:00.000
1900-01-01 07:56:35.000 1900-01-01 20:14:37.000 1900-01-01 08:15:00.000 1900-01-01 19:45:00.000
1900-01-01 07:21:37.000 1900-01-01 20:03:23.000 1900-01-01 07:45:00.000 1900-01-01 19:45:00.000
1900-01-01 07:32:37.000 1900-01-01 21:25:56.000 1900-01-01 07:45:00.000 1900-01-01 21:15:00.000
1900-01-01 08:43:37.000 1900-01-01 22:45:34.000 1900-01-01 09:15:00.000 1900-01-01 22:45:00.000
1900-01-01 11:34:37.000 1900-01-01 23:47:78.000 1900-01-01 11:45:00.000 1900-01-01 23:45:00.000
1900-01-01 12:45:37.000 1900-01-01 20:55:23.000 1900-01-01 13:15:00.000 1900-01-01 22:45:00.000
1900-01-01 14:14:37.000 1900-01-01 23:58:12.000 1900-01-01 14:45:00.000 1900-01-01 23:45:00.000
1900-01-01 15:16:74.000 1900-01-01 23:42:23.000 1900-01-01 15:45:00.000 1900-01-01 23:15:00.000time3,time4根据time1,time2的小时和分钟数确定自身的值
CREATE TABLE nowtime
(
timeL datetime ,
timenow DATETIME
)INSERT INTO nowtime
SELECT '1900-01-01 07:01:37.000' ,''
UNION ALL
SELECT '1900-01-01 07:02:37.000' ,''
UNION ALL
SELECT '1900-01-01 07:16:37.000' ,''
UNION ALL
SELECT '1900-01-01 07:18:37.000' ,''
UNION ALL
SELECT '1900-01-01 07:42:37.000' ,''
UNION ALL
SELECT '1900-01-01 07:58:37.000' ,''
UNION ALL
SELECT '1900-01-01 14:20:37.000' ,''
UNION ALL
SELECT '1900-01-01 14:42:37.000' ,''
UNION ALL
SELECT '1900-01-01 14:02:37.000' ,''
UNION ALL
SELECT '1900-01-01 18:37:37.000' ,''SELECT time1, COALESCE(COALESCE(T.A,T.B),T.C) timenow
FROM
(
SELECT N.time1,DATEADD(MI,
CASE WHEN CAST(RIGHT(CONVERT(VARCHAR(16),time1,120),2) AS INT)
BETWEEN 0 AND 14 THEN 15 END ,time1 ) A,
DATEADD(MI,
CASE WHEN CAST(RIGHT(CONVERT(VARCHAR(16),time1,120),2) AS INT)
BETWEEN 15 AND 45 THEN 44 END ,time1 ) B,
DATEADD(MI,
CASE WHEN CAST(RIGHT(CONVERT(VARCHAR(16),time1,120),2) AS INT)
BETWEEN 45 AND 59 THEN 75 END ,time1 ) CFROM nowtime N
)Ttime1 timenow
----------------------- -----------------------
1900-01-01 07:01:37.000 1900-01-01 07:16:37.000
1900-01-01 07:02:37.000 1900-01-01 07:17:37.000
1900-01-01 07:16:37.000 1900-01-01 08:01:37.000
1900-01-01 07:18:37.000 1900-01-01 08:03:37.000
1900-01-01 07:42:37.000 1900-01-01 08:27:37.000
1900-01-01 07:58:37.000 1900-01-01 09:13:37.000
1900-01-01 14:20:37.000 1900-01-01 15:05:37.000
1900-01-01 14:42:37.000 1900-01-01 15:27:37.000
1900-01-01 14:02:37.000 1900-01-01 14:17:37.000
1900-01-01 18:37:37.000 1900-01-01 19:22:37.000(10 row(s) affected)
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (time1 datetime,time2 datetime,time3 datetime,time4 datetime)
insert into #tb
select '1900-01-01 07:01:37.000','1900-01-01 19:01:37.000' ,null,null union all
select '1900-01-01 07:16:14.000','1900-01-01 19:42:37.000' ,null,null union all
select '1900-01-01 07:42:45.000','1900-01-01 19:35:37.000' ,null,null union all
select '1900-01-01 07:56:35.000','1900-01-01 20:14:37.000' ,null,null union all
select '1900-01-01 07:21:37.000','1900-01-01 20:03:23.000' ,null,null union all
select '1900-01-01 07:32:37.000','1900-01-01 21:25:56.000' ,null,null union all
select '1900-01-01 08:43:37.000','1900-01-01 22:45:34.000' ,null,null union all
select '1900-01-01 11:34:37.000','1900-01-01 23:47:18.000' ,null,null union all
select '1900-01-01 12:45:37.000','1900-01-01 20:55:23.000' ,null,null union all
select '1900-01-01 14:14:37.000','1900-01-01 23:58:12.000' ,null,null union all
select '1900-01-01 15:16:14.000','1900-01-01 23:42:23.000',null,nullselect
time1,time2,
time3 =case when datepart(minute,time1) between 0 and 14 then convert(varchar(14),time1,120)+'15:00.000'
when datepart(minute,time1) between 15 and 44 then convert(varchar(14),time1,120)+'45:00.000'
else convert(varchar(14),dateadd(hour,1, time1),120)+'15:00.000'
end,
time4 =case when datepart(minute,time2) between 0 and 14 then convert(varchar(14),dateadd(hour,-1, time2),120)+'45:00.000'
when datepart(minute,time2) between 15 and 44 then convert(varchar(14), time2,120)+'15:00.000'
else convert(varchar(14),dateadd(hour,1, time1),120)+'45:00.000'
end
from #tb(11 行受影响)
time1 time2 time3 time4
----------------------- ----------------------- ----------------------- -----------------------
1900-01-01 07:01:37.000 1900-01-01 19:01:37.000 1900-01-01 07:15:00.000 1900-01-01 18:45:00.000
1900-01-01 07:16:14.000 1900-01-01 19:42:37.000 1900-01-01 07:45:00.000 1900-01-01 19:15:00.000
1900-01-01 07:42:45.000 1900-01-01 19:35:37.000 1900-01-01 07:45:00.000 1900-01-01 19:15:00.000
1900-01-01 07:56:35.000 1900-01-01 20:14:37.000 1900-01-01 08:15:00.000 1900-01-01 19:45:00.000
1900-01-01 07:21:37.000 1900-01-01 20:03:23.000 1900-01-01 07:45:00.000 1900-01-01 19:45:00.000
1900-01-01 07:32:37.000 1900-01-01 21:25:56.000 1900-01-01 07:45:00.000 1900-01-01 21:15:00.000
1900-01-01 08:43:37.000 1900-01-01 22:45:34.000 1900-01-01 08:45:00.000 1900-01-01 09:45:00.000
1900-01-01 11:34:37.000 1900-01-01 23:47:18.000 1900-01-01 11:45:00.000 1900-01-01 12:45:00.000
1900-01-01 12:45:37.000 1900-01-01 20:55:23.000 1900-01-01 13:15:00.000 1900-01-01 13:45:00.000
1900-01-01 14:14:37.000 1900-01-01 23:58:12.000 1900-01-01 14:15:00.000 1900-01-01 15:45:00.000
1900-01-01 15:16:14.000 1900-01-01 23:42:23.000 1900-01-01 15:45:00.000 1900-01-01 23:15:00.000(11 行受影响)