Yesterday's date as parameter

[DSer]

Hello everybody

My database is Oracle.
I have a table which has a date column.
I want to query against it to get data for yesterday's date, which is already available in the table.
I can do this using an oracle query, but I need a way to pass yesterday's date as a parameter to a SQL query in Oracle OCI stage.
How do I go about doing this?

[thebird]

Define a parameter for the Date in the job - and use it in the User Defined SQL query. Pass it down to the job through the Sequence.

In the Sequence, use a User variable activity to come up with the value for Yesterday's date - from Current Date (Sysdate).

Aneesh

[chulett]

You can certainly do it via a number of methods, but why not use TRUNC(SYSDATE-1) in your query instead if it always needs to be 'yesterday'?

[DSer]

The date can change sometimes, that is the reason why i want to parameterise it.
Craig, Can I use TRUNC(SYSDATE-1) as a parameter value?
How do i give the default value of the parameter as yesterday's date?
If the date changes i should be able to give another date.

[thebird]

The date can change sometimes, that is the reason why i want to parameterise it.
Craig, Can I use TRUNC(SYSDATE-1) as a parameter value?
How do i give the default value of the parameter as yesterday's date?
If the date changes i should be able to give another date.

In that case, instead of the date, why dont you parameterize the number of days (from the SYSDATE)?

Using Craig's method you can put it in as

Code: Select all

TRUNC(SYSDATE-#NumDays#)

in the query.

Aneesh[/code]

[chulett]

Or you could parameterize that entire portion of the query:

Code: Select all

AND DATE_FIELD = #P_DATE_CLAUSE#

Default the value to TRUNC(SYSDATE-1) so that 'normal' runs compute yesterdays date from the current system date. Override the value if need be by passing in a fixed date:

TO_DATE('12/01/2006','MM/DD/YYYY')

Would be one way to do that. Or you could even include the operator in the parameter value so that you could even pass the query an 'in list' of dates or a 'between' to do date ranges. This rather than being "stuck" with it always being an equality check.

All kinds of solutions are available to you.

[DSer]

Nice replies craig and thebird.
Craig, do i need to pass TO_DATE('12/01/2006','MM/DD/YYYY') if i have to override? is there no way i can pass just 12/01/2006 or 12-01-2006

[chulett]

Not if you want the flexibility. TO_DATE in Oracle is like IConv in DataStage and gets you an 'internal' date that can be directly compared to another DATE field without issue. Once you start comparing strings you can cause yourself all kinds of grief.

While there are ways you can 'cheat' and just pass in a date as a string, best if you learn proper syntax that you know will always work no matter who's instance you connect to.

[DSer]

Makes sense, Thanks Craig