Derived Column to obatain only date part of a file (last 8 characters)

Question

Post reply

Derived Column to obatain only date part of a file (last 8 characters)

chrismgarvey

SSC Eights!

Points: 834
More actions
November 11, 2009 at 8:09 am

#212271

Hi All,
I have a file name in the following format: 'Filename_otherinformation_somethingelse_20091111.csv'
How would I got about getting only the date part of this file using a derived column transformation?
I have setup a foreach loop contain and loaded the file name into a variable, I am however a little stuck on howto retrieve the date only from the file.
Many Thanks in advance.
Regards,
Chris

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

If the file extension is consistently 3 characters (pretty usual), then something like this would work:

create table #T (

ID int identity primary key,

FName varchar(256),

FDate as left(right(FName, 12), 8));

insert into #T (FName)

select 'Filename_otherinformation_somethingelse_20091111.csv';

select *

from #T;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 2

Or, instead of creating a table and still using a derived column transformation while assuming the file extension is still 3 characters in length, this will work:

SUBSTRING(@[User::FileName],LEN(@[User::FileName]) - 11,8)

GSquared SSC Guru Points: 260824 More actions · Answer 3

Erik Kutzler (11/11/2009)
Or, instead of creating a table and still using a derived column transformation while assuming the file extension is still 3 characters in length, this will work:
SUBSTRING(@[User::FileName],LEN(@[User::FileName]) - 11,8)

That still assumes the file extension is 3 characters.

I just used the temp table to illustrate the string functions.

If the file extension will vary in length, then first reversing the string and getting the charindex of the period (gets the last period) and subtracting that value from the length, will get you the point to cut off the string with the left function, then use right to get the last 8 characters.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

chrismgarvey SSC Eights! Points: 834 More actions · Answer 4

Hi Eric.

Many thanks for your help!

I went for your 2nd suggestion of using a derived column transformation which worked fine.

Regards,

Chris