22,210
社区成员
发帖
与我相关
我的任务
分享用case划分三字段
表nowtime 有两个字段time1(datetime)，timenow(datetime)
time1 timenow
1900-01-01 07:01:37.000
1900-01-01 07:02:37.000
1900-01-01 07:16:37.000
1900-01-01 07:18:37.000
1900-01-01 07:42:37.000
1900-01-01 07:58:37.000
1900-01-01 14:20:37.000
1900-01-01 14:42:37.000
1900-01-01 14:02:37.000
1900-01-01 18:42:37.000
将分钟分为三个阶段 00-15 15-45 45-00
表nowtime 有两个字段time1(datetime)，timenow(datetime)
time1 timenow
1900-01-01 07:01:37.000 1900-01-01 07:15:00.000
1900-01-01 07:02:37.000 1900-01-01 07:15:00.000
1900-01-01 07:16:37.000 1900-01-01 07:45:00.000
1900-01-01 07:18:37.000 1900-01-01 07:45:00.000
1900-01-01 07:42:37.000 1900-01-01 08:15:00.000
1900-01-01 07:58:37.000 1900-01-01 08:15:00.000
1900-01-01 14:20:37.000 1900-01-01 14:45:00.000
1900-01-01 14:42:37.000 1900-01-01 14:45:00.000
1900-01-01 14:02:37.000 1900-01-01 14:15:00.000
1900-01-01 18:42:37.000 1900-01-01 18:45:00.000
如何根据time1 的分钟，0-15，15-45,45-00把结果添加到timenow中
...全文
请发表友善的回复…
发表回复
哥子谭 2010-09-01
- 打赏
- 举报
使用Case来判断
「已注销」 2010-08-30
- 打赏
- 举报
调整一下时间格式
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 15 THEN 15 END ,time1 ) A,
DATEADD(MI,
CASE WHEN CAST(RIGHT(CONVERT(VARCHAR(16),time1,120),2) AS INT)
BETWEEN 16 AND 45 THEN 45 END ,time1 ) B,
DATEADD(MI,
CASE WHEN CAST(RIGHT(CONVERT(VARCHAR(16),time1,120),2) AS INT)
BETWEEN 46 AND 59 THEN 75 END ,time1 ) C
FROM nowtime N
)T
time1 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)
「已注销」 2010-08-30
- 打赏
- 举报
将4楼 中'1900-01-01 07:01:37.000’都替换了 time1
即：
即：
SELECT time1, COALESCE(COALESCE(T.A,T.B),T.C) timenow
FROM
(
SELECT time1,DATEADD(HH,
CASE WHEN CAST(RIGHT(CONVERT(VARCHAR(16),time1,120),2) AS INT)
BETWEEN 0 AND 15 THEN 15 END ,time1 ) A,
DATEADD(HH,
CASE WHEN CAST(RIGHT(CONVERT(VARCHAR(16),time1,120),2) AS INT)
BETWEEN 16 AND 45 THEN 45 END ,time1 ) B,
DATEADD(HH,
CASE WHEN CAST(RIGHT(CONVERT(VARCHAR(16),time1,120),2) AS INT)
BETWEEN 46 AND 59 THEN 75 END ,time1 ) C
)T「已注销」 2010-08-30
- 打赏
- 举报
SELECT time1, COALESCE(COALESCE(T.A,T.B),T.C) timenow
FROM
(
SELECT time1,DATEADD(HH,
CASE WHEN CAST(RIGHT(CONVERT(VARCHAR(16),'1900-01-01 07:01:37.000',120),2) AS INT)
BETWEEN 0 AND 15 THEN 15 END ,'1900-01-01 07:01:37.000' ) A,
DATEADD(HH,
CASE WHEN CAST(RIGHT(CONVERT(VARCHAR(16),'1900-01-01 07:01:37.000',120),2) AS INT)
BETWEEN 16 AND 45 THEN 45 END ,'1900-01-01 07:01:37.000' ) B,
DATEADD(HH,
CASE WHEN CAST(RIGHT(CONVERT(VARCHAR(16),'1900-01-01 07:01:37.000',120),2) AS INT)
BETWEEN 46 AND 59 THEN 75 END ,'1900-01-01 07:01:37.000' ) C
)T
haxili1919 2010-08-30
- 打赏
- 举报
如果分钟在0-15分的时候，时间为：原来小时+15
如果分钟在15-45分时候，时间为：原来小时+45
如果分钟大于45分时候，时间为：（原来小时+1）+15
如果分钟在15-45分时候，时间为：原来小时+45
如果分钟大于45分时候，时间为：（原来小时+1）+15
路伊阑珊 2010-08-30
- 打赏
- 举报
SELECT DATENAME(minute, GETDATE()) AS 'Month Name'
有点不太明白你的意思 你是要在表中 加以列存储分钟？ 还是 0-15这种形式
jstoic 2010-08-30
- 打赏
- 举报
你给的数据结果好像不正确。
处理的话，使用datepart函数，去分钟，然后用case判断即可。
