Author |
Topic |
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-02-13 : 11:10:29
|
Hello All,I know this has been asked for million times already but I can't seem to get it right.I need to replace just the last character from a string.I have a update_date column in my table and datatype is datetime(). But I need to convert it to varchar and replace last character with 'Z' for the work I am doing. One thing I know is length of update_date column is always 19input string: 2012-11-19 13:53:41.377output I want : 2012-11-19 13:53:41.37ZI have tried using left,right,charindex,substring functions without any luck.Thanks. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-13 : 11:21:50
|
If the update_date column is of data type DATETIME, you cannot store the value with the "Z" in it. DATETIME stores the data in an internal format (a 2-byte representation of the date+time), not as a character type column. You can of course, convert the data to a string on the fly and retrieve it - for example like this:STUFF(CONVERT(VARCHAR(32),update_date,121),23,1,'Z') |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-13 : 11:27:55
|
stuff(convert(varchar(30),update_date,121),len(convert(varchar(30),update_date,121)),1,'Z')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-02-13 : 11:35:29
|
If you always have milliseconds thenconvert(varchar(22),update_date,121) + 'Z' djj |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-02-13 : 11:56:11
|
Wow thank you James,Visakh and djj55 but I have a new problem now.I should have mentioned the whole thing in the beginning itself. Sorry about that.original input string : 2012-11-19 13:53:41.377original output wanted: 2012-11-19T13:53:41.37ZI was doing replace ' ' with 'T' for inserting 'T' before, but now I don't how to get output in single statement merging REPLACE for 'T' and STUFF for 'Z' |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-02-13 : 12:26:25
|
Nevermind, I got it. Thank you all. |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-02-13 : 13:12:18
|
Please post your solution so if someone has a similar problem they know what you did.djj |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-02-13 : 13:33:06
|
Here's the working solution.declare @update_date varchar (100)set @update_date = '2012-11-19 13:53:41.377'select stuff(REPLACE (CONVERT (varchar(100),@update_date,121) ,' ','T'),len(convert(varchar(100),@update_date,121)),1,'Z')Thanks. |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-02-13 : 19:24:28
|
[code]DECLARE @update_date varchar (100)SET @update_date = '2012-11-19 13:53:41.377'SELECT REPLACE (CONVERT (varchar(22),@update_date,121) ,' ','T') + 'Z'[/code] |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2013-02-14 : 12:10:22
|
Thank you ScottPletcher for even better solution. Much appreciated. |
|
|
|