紧急处理..转换~转换~

[q85958341]

select spare,day,H from consume_164

658 30 16
8657 30 16
3149 30 16
15649 30 16
15599 30 16
8823 31 8
22376 31 8
41191 31 17
7746 1 7
2630 1 7
4790 1 7

这批数据是强行恢复过来的~
现在需要导回数据库中~

首先需要将spare转换
例：658 转 6.58
8657 转 86.57

day,H 列
需要合并~成‘2009-10-30 16:00:00.000’

没有月份数据，只有日~这批数据时10月30号~11月2号

[q85958341]

大家发表发表建议~
小弟在此先谢了！

[chuifengde]

SELECT spare*0.01,'2009-10-'+ltrim(DAY)+' '+LTRIM(H)+':00:00.000'

[chuifengde]

SELECT spare*0.01,'2009-'+case when day>29 then '10-' else '11-' end +ltrim(DAY)+' '+LTRIM(H)+':00:00.000'
from consume_164

[nianran520]

--测试数据
declare @table table(spare int,day varchar(10),H varchar(10))
insert into @table
select 658,'30','16' union all        
select 8657, '30'  , '16'     union all      
select 3149  ,    '30'  ,      '16'   union all        
select 15649  ,  '30'   ,     '16'     union all      
select 15599   , '30'   ,     '16'      union all     
select 8823   , '31'    ,    '8'        union all   
select 22376  ,   '31'  ,      '8'      union all         
select 41191  ,   '31' ,       '17'     union all      
select 7746    , '1'   ,    '7'       union all    
select 2630   ,  '1'   ,     '7'        union all   
select 4790   ,   '1'  ,      '7'  
--转换
select convert(dec(9,2),spare)/100 as spare,
case len(day) when 2 then dateadd(hh,convert(int,H),convert(datetime,'2009-10-'+day))
else dateadd(hh,convert(int,H),convert(datetime,'2009-11-0'+day)) end as date
from @table
--结果
----------------------
6.580000	2009-10-30 16:00:00.000
86.570000	2009-10-30 16:00:00.000
31.490000	2009-10-30 16:00:00.000
156.490000	2009-10-30 16:00:00.000
155.990000	2009-10-30 16:00:00.000
88.230000	2009-10-31 08:00:00.000
223.760000	2009-10-31 08:00:00.000
411.910000	2009-10-31 17:00:00.000
77.460000	2009-11-01 07:00:00.000
26.300000	2009-11-01 07:00:00.000
47.900000	2009-11-01 07:00:00.000

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-04 09:52:33
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([spare] int,[day] int,[h] int)
insert [tb]
select 658,30,16 union all
select 8657,30,16 union all
select 3149,30,16 union all
select 15649,30,16 union all
select 15599,30,16 union all
select 8823,31,8 union all
select 22376,31,8 union all
select 41191,31,17 union all
select 7746,1,7 union all
select 2630,1,7 union all
select 4790,1,7
--------------开始查询--------------------------
select  
   spare*0.01 as [price],
   '2009-'+case when day>29 then '10-' else '11-' end +ltrim(DAY)+' '+LTRIM(H)+':00:00.000' as [time]
from 
   tb
----------------结果----------------------------
/*price                                   time
--------------------------------------- -------------------------------------------
6.58                                    2009-10-30 16:00:00.000
86.57                                   2009-10-30 16:00:00.000
31.49                                   2009-10-30 16:00:00.000
156.49                                  2009-10-30 16:00:00.000
155.99                                  2009-10-30 16:00:00.000
88.23                                   2009-10-31 8:00:00.000
223.76                                  2009-10-31 8:00:00.000
411.91                                  2009-10-31 17:00:00.000
77.46                                   2009-11-1 7:00:00.000
26.30                                   2009-11-1 7:00:00.000
47.90                                   2009-11-1 7:00:00.000

(11 行受影响)
 
*/

[q85958341]

谢谢！我除以100应该也对的吧！

[q85958341]

谢谢大家！