SSIS get hour from date

Question

SSIS get hour from date

PSB

SSChampion

Points: 13195
More actions
January 24, 2011 at 3:30 pm

#244182

I have a filename of the format
T2011012412ABC
Where 2011= year
01=Month
24=Date
12=hour
I am using a flat file and the data,month,time changes for each file. I am getting the results for yearMonthDate correct. But need help in the time part (00 to 23)
@[User::DataLoadDir] + "T" + (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) + (LEN((DT_STR,2,1252)(DATEPART("MM",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("MM",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("MM",GETDATE()))) + (LEN((DT_STR,2,1252)(DATEPART("dd",GETDATE()))) == 2 ? (DT_STR,2,1252)(DATEPART("dd",GETDATE())) : "0" + (DT_STR,2,1252)(DATEPART("dd",GETDATE()))) +"ABC" +".log"
Thanks,
PSB

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

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

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

What are you trying to do?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

PSB SSChampion Points: 13195 More actions · Answer 2

I have to make the filename for the flat file dynamic of the given format. I will have it every hour. Is there a better idea to do that ?

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

It's an ungainly expression, but as you've nearly got it completed, I guess there's no good reason to try something else.

Have you tried using

DATEPART("hh",GETDATE())

?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Just a quick addon to Phil's suggestion which is inline with what you're attempting, a quick and dirty alternative:

Use this in an execute SQL task and feed the result set to a local variable in the package, then just throw the variable around in the expressions:

SELECT LEFT( REPLACE( REPLACE( REPLACE( convert( varchar(30), getdate(), 121), '-', ''), ' ', ''), ':', ''), 10)

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

PSB SSChampion Points: 13195 More actions · Answer 5

Thanks. That worked !

Is there any other way to make the flat file name dynamic ? That is not do any of these and just pick up any file from a particular folder . I am using a foreach loop container and set delay valdation of the flat file to True. Also in the expression builder for the connection string property using the following @[User::TW_File_Name] .