Help Converting Datetime field in Oracle/Sql Stored Proc for passing a Date Parameter

[BeeBear2]

Hi,
I'm using Oracle Sql 11g to create a stored procedure which I am then using to create a crystal report.

My problem is with passing the Date Parameter through the stored proc. As Oracle wants to treat the date as a datetime, I can't get the parameter to work.

Parameters are declared as:
P_Start_Date and P_End_Date

The Stored proc has

TO_DATE(timestamp1, 'dd/mm/yyyy') between P_Start_Date AND P_End_Date

But it wont work.


Step-by-step Ideas would be appreciated urgently as its an urgent request and I'm only learning Oracle Sql.

PS - As a side, they want it to pass a default parameter value for each date, being a changeable date based on the sys date.
Start Date = 1st of last month.
End Date = Sys Date

Thanks

 

[Beilstwh]

why not simply make the parameter varchar2 and pass the string (for example) '14-08-2017' and in the procedure simply use to_date(p_timestamp1,'DD-MM-YYYY'). All languages support strings as parameters and this will always work.

Bill
Lead Application Developer
New York State, USA

 

[Andrzejek]

Sys Date in Oracle is actually SYSDATE
[tt]
SELECT SYSDATE FROM DUAL[/tt]
Gives you current date and time,
[tt]
SELECT TRUNC(SYSDATE) AS MYDATE FROM DUAL[/tt]
Gives you just the today's Date

Start Date = 1st of last month - you just need to subtract a month from SYSDATE (there is an Oracle function ADD_MONTHS, just 'add' -1 months ) and set your Start Date to the 1st of that month.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[stefanhei]

But it wont work." is not the best error description. What error message do you see?
If the parameters P_Start_Date and P_End_Date are of datatype DATE I do not see why the condition you mention should not work, unless the string that variable timestamp1 holds does not match the date format specifier 'dd/mm/yyyy'.
To get the first day of the last month you can use the TRUNC function too:

SELECT TRUNC(ADD_MONTHS(sysdate,-1),'MONTH') FROM dual;

PS.: TRUNC(SYSDATE) gives you 00:00:00 as time component. Oracles datatype DATE always contains date and time, but some clients do not display the time component if it is set to midnight.