SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Replace just the last charatcter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssunny
Posting Yak Master

USA
133 Posts

Posted - 02/13/2013 :  11:10:29  Show Profile  Reply with Quote
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

3761 Posts

Posted - 02/13/2013 :  11:21:50  Show Profile  Reply with Quote
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')
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/13/2013 :  11:27:55  Show Profile  Reply with Quote
stuff(convert(varchar(30),update_date,121),len(convert(varchar(30),update_date,121)),1,'Z')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
341 Posts

Posted - 02/13/2013 :  11:35:29  Show Profile  Reply with Quote
If you always have milliseconds then
convert(varchar(22),update_date,121) + 'Z'


djj
Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 02/13/2013 :  11:56:11  Show Profile  Reply with Quote
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'

Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 02/13/2013 :  12:26:25  Show Profile  Reply with Quote
Nevermind, I got it. Thank you all.
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
341 Posts

Posted - 02/13/2013 :  13:12:18  Show Profile  Reply with Quote
Please post your solution so if someone has a similar problem they know what you did.

djj
Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 02/13/2013 :  13:33:06  Show Profile  Reply with Quote
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.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
437 Posts

Posted - 02/13/2013 :  19:24:28  Show Profile  Reply with Quote

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'


Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 02/14/2013 :  12:10:22  Show Profile  Reply with Quote
Thank you ScottPletcher for even better solution. Much appreciated.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000