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

Passing Date parameters to Oracle Expand / Collapse
Author
Message
Posted Thursday, May 6, 2010 4:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, November 8, 2014 2:59 AM
Points: 21, Visits: 86
I have defined in my DTS package several date Global Variables, these hold dates in various formats and types.

My problem is that sometimes when I run my query/task the dates do not get past to Oracle in the correct manner and I have to keep messing with the task to get it to work.

For example

Global Variables defined as

GVSysdate Date 01/05/2010
GVSysdate_Str String 01/05/2010

Various SQL statements that randomly work

Trunc(tab1.status_date) = to_date(?,'DD/MM/YYYY') (? is defined as GVSysdate_Str)

OR

Trunc(tab1.status_date) = to_date(to_Char(?,'DD/MM/YYYY'),'DD/MM/YYYY')
(? is defined as GVSysdate)



Does anybody know of a definitive method of using dates from SQL2000 to Oracle when they are defined as Global variables and are used as input parameters to tasks, as I'm wasting about 1hr perday trying to get these tasks working !


Thanks in advance






Post #917504
Posted Wednesday, July 14, 2010 6:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
The secret is to be sure date_field and mask have matching formats

TO_DATE(date_field,mask)
like:
to_date('07/14/2010','mm/dd/yyyy') <= This is good
to_date('07142010','mmddyyyy') <= This is good
to_date('20100714','yyyymmdd') <= This is good
to_date('14/07/2010','mm/dd/yyyy') <= This is BAD


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #952277
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse