| Author |
Topic |
|
mrm23
Posting Yak Master
198 Posts |
 Posted - 2010-04-23 : 05:17:00
|
| Hi All, I have a DURATION column in one of my tables which is in nvarchar.that column shows the duration alloted to a praticular task. The data we get is in the form 10,20, 60 etc.I want to store as 00:10 if it is 10 mins.... if it is 60 it must be 01:00.How to do this? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-23 : 05:22:14
|
[code]select convert(varchar(5), dateadd(minute, duration, 0), 108)from yourtable[/code]if the duration is more than 1 day, you might want to prefix that with days
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-23 : 05:24:24
|
| Thanks KHTan. But the DURATION col is in NVARCHAR and i got the foll error:Argument data type nvarchar is invalid for argument 2 of dateadd function. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-23 : 05:26:38
|
convert it to integer first . select convert(varchar(5), dateadd(minute, convert(int, duration), 0), 108)from yourtable
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-23 : 05:31:24
|
| Thanks a lot! It works... i needed this urgently... thanks |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-23 : 05:40:45
|
| I am using this in a SP. I have a cursor in that and this conversion is to be made in that cusor query. now when i execute the SP, i am getting conversion error. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-23 : 05:42:11
|
post the lines of codes that gives the error
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-23 : 05:42:42
|
| I think i need to give you one more detail. The client DB has that column in INT.We must store as NVARCHAR.So, the INT value 10 should be stored as NVARCHAR "00:10" |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-23 : 05:44:21
|
| My cursor query is : SELECT tc.contactid,tc.companyname, th.historytypeid, th.endtime, convert(nvarchar(5) ,dateadd(minute, th.duration, 0),108) , th.regarding, tht.name FROM tp.dbo.tbl_contact tc JOIN tp.dbo.tbl_contact_history tch ON tc.contactid = tch.contactid JOIN tp.dbo.tbl_history th ON th.historyid = tch.historyid JOIN tp.dbo.tbl_historytype tht ON tht.historytypeid = th.historytypeid WHERE category IS NOT NULL AND th.historytypeid<>-1***************************************************************************************************This works fine individually but when i execute that i get the foll error:Conversion failed when converting the nvarchar value '00:00' to data type int. |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-04-23 : 05:50:13
|
| Sorry it was my mistake. I have declared the variable wrongly.Thanks for your time. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-23 : 06:02:24
|
you are welcome
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-05-12 : 07:28:40
|
| hi all,i have got the same issue ....but now i need to only update that duration column.it is in nvarchar datatype. so if i give convert(nvarchar(5),dateadd(minute,duration,0),108) ,it is giving me the following error: Argument data type nvarchar is invalid for argument 2 of dateadd function.I know this is very silly , but m blank... :( please help |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-12 : 07:54:31
|
tryconvert(varchar(5), dateadd(minute, convert(int, duration), 0), 108)
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-05-12 : 07:56:43
|
| hi khtan,i tried this too...but i am getting the same error :( |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-05-12 : 08:43:14
|
quote:
Originally posted by mrm23
hi khtan,i tried this too...but i am getting the same error :(
Post the full code you usedMadhivananFailing to plan is Planning to fail |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2010-05-12 : 08:48:53
|
| hi all,thanks for your time...i got it...i had to convert it to INTEGER first....thanks |
|
|
|
|
displaying time - solved