SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Passing Date parameters to Oracle


Passing Date parameters to Oracle

Author
Message
itwhiz
itwhiz
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 95
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14581 Visits: 4639
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search