Substring Date Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Substring Date Function

Author

Topic

theKid27
Starting Member

21 Posts

Posted - 2013-03-14 : 23:09:18

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

7 Posts

Posted - 2013-03-15 : 03:13:19

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

theKid27
Starting Member

21 Posts

Posted - 2013-03-15 : 03:21:58

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-15 : 03:38:27

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.

theKid27
Starting Member

21 Posts

Posted - 2013-03-15 : 05:12:45

thanks webfred:)

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-03-15 : 06:21:52

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'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-15 : 06:46:56

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources