May 12, 2010 at 5:53 am
am trying to update my table with a simple conversion but I am getting error.
I have a duration column in my table which is stored as 0,10,45,60 etc.
This is have to convert to time format . i.e like this: 00:00,00:10 and so on. If it exceeds one hour then it should be 01:00 (when it is 60)
I wrote this conversion function first:
update hc_task_manager set duration = convert(nvarchar(5),dateadd(minute,duration,0),108)
but I got this error:
Argument data type nvarchar is invalid for argument 2 of dateadd function.
My column datatype is nvarchar itself. What to do?
May 12, 2010 at 6:12 am
Malaivaka, tel us if the following code helps you
SELECT CASE DURATION
WHEN '60' THEN '01:00'
WHEN '0' THEN '00:00'
ELSE '00:'+CAST(DURATION AS VARCHAR) END DURATION_MINS
FROM TABLE
Cheers!
~Edit : Corrected the code!
May 12, 2010 at 6:23 am
hi,
thanks for your reply. i dont think it works practically as there are many possible durations...or even if we think of 5 min duration the least, i will have to write it 12 times... and what if the input is 130?
m sorry if i have not completely understood your reply and have written this in haste...
but now i got solution and here is what i did:
i just converted that column to int.
update hc_task_manager set duration = convert(varchar(5),dateadd(minute,cast(duration as int),0),108)
Thanks...
May 12, 2010 at 6:35 am
malavika.ramanathan (5/12/2010)
hi,thanks for your reply. i dont think it works practically as there are many possible durations...or even if we think of 5 min duration the least, i will have to write it 12 times... and what if the input is 130?
Malavika, you did not specify or allow us to know what your input values are, dear! 🙁
but now i got solution and here is what i did:
Anyways, as you had got a solution, now am happy 🙂
Enjoy dear!
Cheers!
May 12, 2010 at 6:38 am
oh m sorry...
anyway thanks for your reply....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply