Passing Date parameters to Oracle

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Working with Oracle » Passing Date parameters to Oracle

Passing Date parameters to Oracle

Rate Topic

Display Mode

Topic Options

Author

Message

itwhiz

Posted Thursday, May 06, 2010 4:49 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 02, 2013 4:55 AM
Points: 11, Visits: 45

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

PaulB-TheOneAndOnly

Posted Wednesday, July 14, 2010 6:44 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 2,982, Visits: 4,397

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 »

Permissions