Date formating in SSIS

Question

Date formating in SSIS

rayash16

Ten Centuries

Points: 1054
More actions
April 8, 2009 at 12:54 pm

#199106

Hi,
I have a situation where i take extract the information about quarter and year from the name of the flat file which i use later in staging table and fact table.
the name of the file is like AB_Q1_08.txt
i use substring to extract info as
select SUBSTRING(AB_Q1_08.txt,9,2)
select Replace(Substring(AB_Q1_08.txt,12,5),'_','-')
result what i get it ,AB and Q1-08 respectively.
is there any way i can save Q1-08 in date format ,say quarter or year.
i tried breaking year as 2008 but it also stores as varchar.
Can somebody tell me how do i convert it to date format.
right now i am trying to run proc.
thanks
ashish

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

I'd say you need a lookup table that stores what date you want "Q1" to turn into. Would that be 1 Jan, 31 Mar, something else? With a lookup table, you can join that part to the table, add in the year, and have a date.

- 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

rayash16 Ten Centuries Points: 1054 More actions · Answer 2

thanks fro the reply.

Let me rephrase the question.

Is it possible to store say soemthign like 107 as date .meaning it doesnt have any date .just month and year.

can i format that to date time.

i have some ideas but not able to implent like with datepart i can find out the quarter only thing is i dont have a propre mmddyy date format

i just have myy.

about lookup table i dont want o change those dates to some other dates.coz it goes like that only in the fact and are used to find the time range.

do u have any idea how can i store something like that in date format.

let me summarise

myy as varchar .i want to change to anything in date either quarter or year.so that i can query based on date

GSquared SSC Guru Points: 260824 More actions · Answer 3

You won't be able to store that in date format, unless you assign it a specific date. Since you don't want to do that, your best bet is to store it as a string. You could create a user-data type (Books Online has directions on that), which would allow you to define it a bit more, but I haven't found those very useful, personally.

- 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

rayash16 Ten Centuries Points: 1054 More actions · Answer 4

thanks a lot

you are about user data type.exactly right but i some how managed it buy breaking it into two part and concatenating the '20' part for the year that and put a index on two column which i created from that 'q106' values so now it looks like 'q1' and '2006'.

I know its lame but whatever works.

i got the work done..

thanks