Yesterday's date as parameter
Moderators: chulett, rschirm, roy
Yesterday's date as parameter
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?
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?
In that case, instead of the date, why dont you parameterize the number of days (from the SYSDATE)?DSer wrote: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.
Using Craig's method you can put it in as
Code: Select all
TRUNC(SYSDATE-#NumDays#)
Aneesh[/code]
Or you could parameterize that entire portion of the query:
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.
Code: Select all
AND DATE_FIELD = #P_DATE_CLAUSE#
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers