By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

how to insert the value of a function in the coloum

P: 4
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
Dec 9 '09 #1
Share this Question
Share on Google+
4 Replies


nbiswas
100+
P: 149
Let me know if this helps

Expand|Select|Wrap|Line Numbers
  1. declare @t table(id int identity,ElaspedSecond varchar(100))
  2. insert into @t 
  3.     select '01/14/2008 16:50:41.020' union all
  4.     select '01/15/2008 16:51:42.020' union all
  5.     select '01/16/2008 16:52:43.020' union all
  6.     select '01/17/2008 16:53:44.020' 
  7.  
  8. declare @i int
  9. set @i = 12345
Query:

Expand|Select|Wrap|Line Numbers
  1. update @t
  2. set ElaspedSecond = (SELECT convert(char(8), dateadd(second, @i, 0), 114))
  3.  
  4. select * from  @t 
Output:

id ElaspedSecond
Expand|Select|Wrap|Line Numbers
  1. 1    03:25:45
  2. 2    03:25:45
  3. 3    03:25:45
  4. 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

Expand|Select|Wrap|Line Numbers
  1. id    ElaspedSecond
  2. 1    1900-01-01 03:25:45.000
  3. 2    1900-01-01 03:25:45.000
  4. 3    1900-01-01 03:25:45.000
  5. 4    1900-01-01 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
Dec 9 '09 #2

P: 4
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
Dec 10 '09 #3

ck9663
Expert 2.5K+
P: 2,878
Here, read this


Good luck!!!


--- CK
Dec 10 '09 #4

nbiswas
100+
P: 149
Try this

Sample data

Expand|Select|Wrap|Line Numbers
  1. declare @t table(id int identity,TimeInSecond int,TimeInHHMMSS time)
  2. insert into @t select 30088,null union all select 6000,null union all select 500,null
  3.  
Solution in Sql Server (2005+)[Cte based solution]
Expand|Select|Wrap|Line Numbers
  1. ;with cte as
  2. (
  3.     select
  4.     TimeInSecond
  5.     ,convert(char(8), dateadd(second, TimeInSecond, 0), 114)newtime
  6.     from @t
  7. )
  8.  
  9. update @t 
  10. set TimeInHHMMSS = c.newtime
  11. from cte c
  12. inner join @t t
  13. on t.TimeInSecond = c.TimeInSecond
  14.  
  15. select * from @t
Solution: Without CTE(work in any version of SQL SERVER)

Expand|Select|Wrap|Line Numbers
  1. update @t 
  2. set TimeInHHMMSS = c.newtime
  3. from 
  4. (select
  5.     TimeInSecond
  6.     ,convert(char(8), dateadd(second, TimeInSecond, 0), 114)newtime
  7.     from @t
  8. )c
  9. inner join @t t
  10. on t.TimeInSecond = c.TimeInSecond
  11.  
  12. select * from @t
Output:

Expand|Select|Wrap|Line Numbers
  1. id    TimeInSecond    TimeInHHMMSS
  2. 1    30088    08:21:28.0000000
  3. 2    6000    01:40:00.0000000
  4. 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
Dec 11 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.