| Author |
 Topic  |
|
|
theKid27
Starting Member
16 Posts |
 Posted - 03/14/2013 : 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
India
7 Posts |
Posted - 03/15/2013 : 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 |
Edited by - RajanThan on 03/15/2013 03:14:00 |
 |
|
|
theKid27
Starting Member
16 Posts |
Posted - 03/15/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 03/15/2013 : 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
16 Posts |
Posted - 03/15/2013 : 05:12:45
|
| thanks webfred:) |
|
|
|
waterduck
Aged Yak Warrior
Malaysia
805 Posts |
Posted - 03/15/2013 : 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' |
Edited by - waterduck on 03/15/2013 06:22:47 |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 03/15/2013 : 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. |
|
|
| |
Topic |
|
|
|
Substring Date Function
