September 24, 2010 at 7:37 pm
TRIM and convert to DATEQuestion:
I have a 10000 rows in a filed of Type varchar(30)
Smaple sat looks like this
ERC2009Q1
LRC2009Q4
SRC2009Q3
TRC2009Q2
ERC2009Q1
ERC2009Q1
I need it to be converted to date.
Where right most charecter represents months
1-----> 3
2-----> 6
3------> 9
4------->12
and 4 to 7th charecters represent year
and defaulting day to 1 in every case
so the o/p would look like
ERC2009Q1-------------------->03/01/2009
LRC2009Q4-------------------->12/01/2009
C2009Q3-------------------->09/01/2009
TRC2009Q2-------------------->06/01/2009
ETHYRC2009Q1-------------------->03/01/2009
TTSERC2009Q1-------------------->03/01/2009
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
September 24, 2010 at 7:41 pm
Second set of data is exact.
The date part has to be extracted from right.
Coz the number of prefix charecters vary for each record.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
September 24, 2010 at 9:09 pm
Declare @sample table (data varchar(30))
insert into @sample
select 'ERC2009Q1' union all
select 'LRC2009Q4' union all
select 'C2009Q3' union all
select 'TRC2009Q2' union all
select 'ETHYR2009Q1' union all
select 'TTSERC2009Q1'
select right('0'+cast(right(data,1)*3 as varchar),2)+'/01/'+left(right(data,6),4)
from @sample
I don't understand why this was difficult for you, because you seem to know conceptually exactly what needed to be done? Are you unfamiliar with SQL functions?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply