Oracle date format

[Tatiana]

I have a job that extracts data from Oracle table to SQL. There is a where clause in the ORAOCI stage -- (TO_CHAR(AS_OF_DATE,'MM/DD/YYYY') = '#ASOF#'), where ASOF is a parameter. Is there a way to modify this criteria statement to use Oracle formatted dates (Ex: 31-May-2006), instead of 5/31/06? Would it speed up the processing time? The job is slow right now. Thanks.

[chulett]

There's no such thing a 'Oracle formatted dates'... and yes, you could override the date format but that in itself wouldn't speed anything up. What might is this:

Code: Select all

AS_OF_DATE = TO_DATE('#ASOF#','MM/DD/YYYY')

Again, the format of the date won't matter as long as it matches the mask. If you are using a parameter type of 'Date' (which you should be, IMHO) the format will be forced to YYYY-MM-DD.

[Tatiana]

Thanks, I'll try this.

Code: Select all

AS_OF_DATE = TO_DATE('#ASOF#','MM/DD/YYYY')

[Tatiana]

If I already have my ASOF parameter in mm/dd/yyyy format, do I need to have TO_DATE? I can just say
AS_OF_DATE = '#ASOF#', right?

Are there ways to improve performance on DS jobs that pull data from Oracle? The table in Oracle has data for multiple months and I just pull one month. The same select statement runs a lot faster in Oracle. The job used to ran faster when Oracle table had fewer months of data.

[chulett]

If I already have my ASOF parameter in mm/dd/yyyy format, do I need to have TO_DATE? I can just say
AS_OF_DATE = '#ASOF#', right?

No... unless your date parameter's format matches the NLS date format of the database, but I for one would never make any assumptions there.