时间类型转换和数值换算问题，高手帮帮

[veras]

一、2006-8-28 10:3:47怎样转换为060828100347
二、小于60按1算，61-120按2算，121-160按3算，大于等于161按4算

初学sql,希望大家不惜赐教，谢谢^_^

[shawnwan]

declare @dt as datetime
set @dt = '2006-8-28 10:3:47'
select replace(replace(replace(right(convert(varchar(19),@dt,120),17),'-',''),':',''),' ','') as 结果

[marco08]

declare @dt datetime
set @dt='2006-8-28 10:3:47'
select right(convert(char(8), @dt, 112), 6)+replace(convert(char(8), @dt, 108), ':', '')

--result
060828100347

(1 row(s) affected)

[marco08]

--第二個問題可以用 case when then else end 判斷

[veras]

谢谢谢谢各位的热心帮助

[cpp2017]

select convert(varchar(20),Cast('2006-8-28 10:3:47' as datetime),12) + ( select replace( convert(varchar(20),Cast('2006-8-28 10:3:47' as datetime),108),':',''))

[cpp2017]

case when 字段<60 then 1 else case when 字段<120 then 2 else case when 字段<160 then 3
else 4 end end end

[dawugui]

declare @dt as datetime
set @dt = '2006-8-28 10:3:47'
select replace(replace(replace(right(convert(varchar(19),@dt,120),17),'-',''),':',''),' ','') as 结果

结果
------------
060828100347

（所影响的行数为 1 行）

[dawugui]

小于60按1算，61-120按2算，121-160按3算，大于等于161按4算

select case when 字段 <= 60 then 1 --这里是小于等于吧?
when 字段 between 61 and 120 then 2
when 字段 between 121 and 160 then 3
when 字段 >= 161 then 4
end as 字段
from tb

[dawugui]

declare @字段 as int
set @字段 = 60

select case when @字段 <= 60 then 1 --这里是小于等于吧?
when @字段 between 61 and 120 then 2
when @字段 between 121 and 160 then 3
when @字段 >= 161 then 4
end as 字段
字段
-----------
1

（所影响的行数为 1 行）

declare @字段 as int
set @字段 = 100

select case when @字段 <= 60 then 1 --这里是小于等于吧?
when @字段 between 61 and 120 then 2
when @字段 between 121 and 160 then 3
when @字段 >= 161 then 4
end as 字段
字段
-----------
2

（所影响的行数为 1 行）


declare @字段 as int
set @字段 = 150

select case when @字段 <= 60 then 1 --这里是小于等于吧?
when @字段 between 61 and 120 then 2
when @字段 between 121 and 160 then 3
when @字段 >= 161 then 4
end as 字段

字段
-----------
3

（所影响的行数为 1 行）


declare @字段 as int
set @字段 = 250

select case when @字段 <= 60 then 1 --这里是小于等于吧?
when @字段 between 61 and 120 then 2
when @字段 between 121 and 160 then 3
when @字段 >= 161 then 4
end as 字段
字段
-----------
4

（所影响的行数为 1 行）