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
 General SQL Server Forums
 New to SQL Server Programming
 Substring Date Function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

theKid27
Starting Member

21 Posts

Posted - 03/14/2013 :  23:09:18  Show Profile  Reply with Quote
Hi Experts/Members

I have a prob here which is to sub-string the date.
My current data is like this 'Feb-2013'
how can i sub-string into this 'Feb-13'


I tried this
REPLACE(RIGHT(CONVERT(VARCHAR(9), [TestDate], 6), 6), ' ', '-')
but the output seems like incorrect.

Kindly provide some advice.. Appreciate for the help

RajanThan
Starting Member

India
7 Posts

Posted - 03/15/2013 :  03:13:19  Show Profile  Reply with Quote
Hi,

Your query is not throwing any error, it is bringing exact data for me.
I just ran your query and got the result you wanted ("Feb-13"). See the examples below,

select REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE() , 6), 6), ' ', '-') as Date --Mar-13
select REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE()-30 , 6), 6), ' ', '-') as Date --Feb-13
select REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE()-50 , 6), 6), ' ', '-') as Date --Jan-13
select REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE()-100 , 6), 6), ' ', '-') as Date --Dec-12
select REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE()+150 , 6), 6), ' ', '-') as Date --Aug-13

If you are still getting the error check the data type of your input [TestDate]


Thanks

Rajan

Edited by - RajanThan on 03/15/2013 03:14:00
Go to Top of Page

theKid27
Starting Member

21 Posts

Posted - 03/15/2013 :  03:21:58  Show Profile  Reply with Quote
Hi Rajan,

Yes the query run the correct data for me.

Sorry may b i've explained in an incorrect way..

Actually I have 2 tables let's make it as Table1 and Table2.

I want to copy the data from Table1 to Table2.

Which in Table1 the data is [Feb-2013] but when it is insert into Table2 i want the format as [Feb-13].

Any advice?

Thanks



Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 03/15/2013 :  03:38:27  Show Profile  Visit webfred's Homepage  Reply with Quote
declare @testvalue varchar(30)
set @testvalue='Feb-2013'

select
replace(@testvalue,right(@testvalue,4),replace(right(@testvalue,4),left(right(@testvalue,4),2),''))



Too old to Rock'n'Roll too young to die.
Go to Top of Page

theKid27
Starting Member

21 Posts

Posted - 03/15/2013 :  05:12:45  Show Profile  Reply with Quote
thanks webfred:)
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 03/15/2013 :  06:21:52  Show Profile  Reply with Quote
declare @testvalue varchar(30)
set @testvalue='Feb-2020'

select replace(@testvalue,right(@testvalue,4),replace(right(@testvalue,4),left(right(@testvalue,4),2),''))

result = 'Feb-'

select stuff(@testvalue, 5, 2, '')

result = 'Fen-20''Feb-20'

Edited by - waterduck on 03/15/2013 06:22:47
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8764 Posts

Posted - 03/15/2013 :  06:46:56  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Originally posted by waterduck

declare @testvalue varchar(30)
set @testvalue='Feb-2020'

select replace(@testvalue,right(@testvalue,4),replace(right(@testvalue,4),left(right(@testvalue,4),2),''))

result = 'Feb-'

select stuff(@testvalue, 5, 2, '')

result = 'Fen-20''Feb-20'


Very good catch from an old duck


Too old to Rock'n'Roll too young to die.
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