how to insert the value of a function in the coloum

[nirmalthiruvathilil]

I have a coloumn ElaspedSecond
i have converted this Second to time
by

declare @i int
set @i = 12345
SELECT convert(char(8) , dateadd(second, @i, 0), 114)

now how can i replace the ElaspedSecond with i in all rows

[nbiswas]

Let me know if this helps

declare @t table(id int identity,ElaspedSecond varchar(100))
insert into @t 
	select '01/14/2008 16:50:41.020' union all
	select '01/15/2008 16:51:42.020' union all
	select '01/16/2008 16:52:43.020' union all
	select '01/17/2008 16:53:44.020' 
	
declare @i int
set @i = 12345

Query:

update @t
set ElaspedSecond = (SELECT convert(char(8), dateadd(second, @i, 0), 114))

select * from  @t

Output:

id ElaspedSecond

1	03:25:45
2	03:25:45
3	03:25:45
4	03:25:45

Note: - I have taken the Elapsedtime column's datatype as varchar(100). If it is datetime, then the output will be

id	ElaspedSecond
1	[B]1900-01-01[/B] 03:25:45.000
2	[B]1900-01-01[/B] 03:25:45.000
3	[B]1900-01-01[/B] 03:25:45.000
4	[B]1900-01-01[/B] 03:25:45.000

Means since it is declared as datetime, so it needs to be in the proper format.(yyyy-mm-dd hh:mm:ss). Since, the update command is only supplying teh hh:mm:ss, so it is taking 1900-01-01 as some default date and is updating only the time part.

Work Around:

If using Sql Server 2008


Declare the ElaspedTime datatype as time . Your purpose will be accomplished.

Alternate Approach:

Declare another column of type varchar and update that column.


Hope this helps

[nirmalthiruvathilil]

sorry Mr. nivas i would like to make it much more clear..

i have a column in my database which gives time in seconds.
i want to convert the time in second to HH:MM:SS and store it into a new coloum which is next to the TimeInSeconds column

id TimeInSecond TimeInHHMMSS
1 30088 NULL
2 6000 NULL
3 500 NULL

i have to store 9

[ck9663]

Here, read this


Good luck!!!


--- CK

[nbiswas]

Solution to how to insert the value of a function in the coloum

Try this

Sample data

declare @t table(id int identity,TimeInSecond int,TimeInHHMMSS time)
insert into @t select 30088,null union all select 6000,null union all select 500,null

Solution in Sql Server (2005+)[Cte based solution]

;with cte as
(
	select
	TimeInSecond
	,convert(char(8), dateadd(second, TimeInSecond, 0), 114)newtime
	from @t
)

update @t 
set TimeInHHMMSS = c.newtime
from cte c
inner join @t t
on t.TimeInSecond = c.TimeInSecond

select * from @t

Solution: Without CTE(work in any version of SQL SERVER)

update @t 
set TimeInHHMMSS = c.newtime
from 
(select
	TimeInSecond
	,convert(char(8), dateadd(second, TimeInSecond, 0), 114)newtime
	from @t
)c
inner join @t t
on t.TimeInSecond = c.TimeInSecond

select * from @t

Output:

id	TimeInSecond	TimeInHHMMSS
1	30088	08:21:28.0000000
2	6000	01:40:00.0000000
3	500	00:08:20.0000000

Note: - I have taken TimeInHHMMSS field's datatype as time(Sql Server 2008's datatype). you can change that to varchar in which case the output will be

id TimeInSecond TimeInHHMMSS
1 30088 08:21:28
2 6000 01:40:00
3 500 00:08:20
Hope this helps