| Author |
 Topic  |
|
|
ssunny
Posting Yak Master
USA
130 Posts |
 Posted - 02/13/2013 : 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 19
input string: 2012-11-19 13:53:41.377
output I want : 2012-11-19 13:53:41.37Z
I have tried using left,right,charindex,substring functions without any luck.
Thanks. |
|
|
James K
Flowing Fount of Yak Knowledge
1488 Posts |
Posted - 02/13/2013 : 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
India
47040 Posts |
Posted - 02/13/2013 : 11:27:55
|
stuff(convert(varchar(30),update_date,121),len(convert(varchar(30),update_date,121)),1,'Z')
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
djj55
Yak Posting Veteran
USA
84 Posts |
Posted - 02/13/2013 : 11:35:29
|
If you always have milliseconds then
convert(varchar(22),update_date,121) + 'Z'
djj |
|
|
|
ssunny
Posting Yak Master
USA
130 Posts |
Posted - 02/13/2013 : 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.377
original output wanted: 2012-11-19T13:53:41.37Z
I 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
USA
130 Posts |
Posted - 02/13/2013 : 12:26:25
|
| Nevermind, I got it. Thank you all. |
|
|
|
djj55
Yak Posting Veteran
USA
84 Posts |
Posted - 02/13/2013 : 13:12:18
|
Please post your solution so if someone has a similar problem they know what you did.
djj |
|
|
|
ssunny
Posting Yak Master
USA
130 Posts |
Posted - 02/13/2013 : 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
Yak Posting Veteran
USA
78 Posts |
Posted - 02/13/2013 : 19:24:28
|
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'
|
|
|
|
ssunny
Posting Yak Master
USA
130 Posts |
Posted - 02/14/2013 : 12:10:22
|
| Thank you ScottPletcher for even better solution. Much appreciated. |
|
|
| |
Topic |
|
Replace just the last charatcter
