SSIS - datetime manipulation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » SSIS - datetime manipulation

SSIS - datetime manipulation

Rate Topic

Display Mode

Topic Options

Author

Message

musclewindows_25

Posted Tuesday, September 04, 2012 3:48 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 4:12 PM
Points: 2, Visits: 11

Hi you all.

I have this task that I'm working on right now, which is to load flat file into an oracle table. I'm using SSIS to do the task.

My issue is the datetime column. The data is in eastern time and I want to convert it into either pacific or mountain time depending on the store number.

this is what I mean: If store_# is 100-400 then convert the time into pacific time
store_# 401-600 then convert time into mountain time

It is possible to do this in derived column? Please help. Thanks

Post #1354231

Evil Kraig F

Posted Tuesday, September 04, 2012 4:20 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:29 PM
Points: 5,678, Visits: 6,128

Yes, but you'll end up doing it in a pair of derived structures because I can't remember offhand if you can embed an if-switch into the DATEADD function.

First, derive the column to a negative x numeric to pass to the next structure, which will dateadd( "h", <newcolumn>, <existingDateTime>)

That'll remove your hours. From there, format as needed to make Oracle happy.

- 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1354242

musclewindows_25

Posted Wednesday, September 05, 2012 9:18 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 4:12 PM
Points: 2, Visits: 11

Something like this?

Storeno >= 100 && Storeno <= 400 ? DateAdd("hh",ABS(DateDiff("hh", GetDate(), GetUTCDate()) - 5), DateAdd("mm", -7.00 * 60, GetUTCDate())) : DateAdd("hh",ABS(DateDiff("hh", GetDate(), GetUTCDate()) - 5), DateAdd("mm", -8.00 * 60, GetUTCDate()

Post #1354653

« Prev Topic | Next Topic »

Permissions