How to convert/re-format column values

[newtosql]

I need to get the values from a column (small example shown on the left) convert them (it "sort-of" represents minutes) from an excel file into a new sql format (on the right) which represents a new incrementing ID (note: single digits always prefixed by '0') and then hours (HH) and then the Minutes(MM) from the far left column. Any ideas?


Field ID/HH:MM
010000D000 (1 mins) 01/00:01
000700D000 (7 mins) 02/00:07
001000D000 (10 mins) 03/00:10
001200D000 (12 mins) 04/00:12

 

[iker3000]

Hi! I don't know where you are getting the ID from, so I assume, that you have a table with a column ID which is an IDENTITY column and a column named Field which contains the given data. IF FIELD is in format HHMM00D000, you can get the specified format like

SELECT
CASE
WHEN ID > 9 THEN ''
ELSE '0'
END
+ CAST(ID AS nvarchar) + '/'
+ SUBSTRING(Field, 1, 2) + ':'
+ SUBSTRING(Field, 3, 2)
FROM
(SELECT 1 AS ID, '000700D000' AS Field) AS Data

where you only have to replace (SELECT 1 AS ID, '000700D000' AS Field) to yours table name.

Iker

 

[ClaireHCM]

Try this.I assumed you want to have a new table that display the results.

1.Create table
create table a([ID/HH:MM] varchar(20))

2.User defined function

create function dbo.format(@field varchar(10))
returns varchar(20)
as begin
declare @rowcount int
set @rowcount = (select count(*) from a)
declare @id1 int
set @id1 = @rowcount+1
declare @id2 varchar(2)
if len(@id1) = 1
begin
set @id2 = '0'+convert(varchar,@id1)
end
if len(@id1) = 2
begin
set @id2 = convert(varchar,@id1)
end
declare @hh varchar(2)
set @hh = substring(@field,1,2)
declare @mm varchar(2)
set @mm = substring(@field,3,2)
declare @string varchar(20)
set @string = @id2+'/'+@hh+':'+@mm

return(@string)
end

3.Insert values from excel sheet

insert into a select dbo.format('001200D000')
insert into a select dbo.format('010000D000')
insert into a select dbo.format('000700D000')

4. Returns
ID
--------------------
01/00:70
02/00:70
03/00:12

Does this match what you want?

 

[SQLBill]

How does this: 010000D000 equal this: 01/00:01
when this: 000700D000 equals this: 02/00:07 ???

To me, it looks like the first example should equal: 01/01:00 and be one hour not one minute.

HHMMSS
010000D000
000700D000
001000D000
001200D000

-SQLBill