May 6, 2010 at 4:49 pm
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
July 14, 2010 at 6:44 am
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.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply