Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS - datetime manipulation Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 3:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, September 4, 2012 4:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:08 PM
Points: 5,401, Visits: 7,514
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
Posted Wednesday, September 5, 2012 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse