AND (H.STARTDATE BETWEEN TO_DATE(‘08/01/2009’,‘MM/DD/YYYY’) AND TO_DATE(‘08/31/2009’,‘MM/DD/YYYY’))
How can i change this line so that when i run it it prompts me to choose a date range from a calendar or by user input?
Thanks for looking.
I looked around but couldnt really find a clear way to prompt a date input for my query. Im using TDA 2.5 Here is what im trying to do:
select …
from…
where…
AND :date2)
Once I did that I was prompted to enter the dates I want to use.
I hope this helps.
Mark
BETWEEN :date1
You need to change your date fields (‘08/01/2009’,‘MM/DD/YYYY’) to bind variables, something similar to this:
Thanks for the response. Your are correct, bind values are the way to go. If you want to use the query in automation you will need to use the TO_CHAR function as you can only use strings and numbers in automation variables.
I forgot about this posting…got lost in the shuffle.
Debbie
and * cd.stardate between :date1 and* :date2
When i do this it gives me this error:
ORA-01858: a non-numeric character was found where a numeric was expected
so this is how it should look?
WHERE (ORDERSTEST.ORDER_DATE BETWEEN :adate AND :bdate)
FROM DEB.ORDERSTEST ORDERSTEST
Your syntax looks OK. I use the Query Builder to build the query. Use the where condition editor and change the operator to Between and then add a colon and bind name.
My Query syntx looks like this.
SELECT ORDERSTEST.ORDER_ID, ORDERSTEST.ORDER_DATE, ORDERSTEST.CONTACT_ID
See attached.
When prompted for the bind values make sure and change the bind data type to DATE. When you do this you can then use a date picker to select the date.
See attached.
Debbie
WHERE (ORDERSTEST.ORDER_DATE BETWEEN :adate AND :bdate)
FROM DEB.ORDERSTEST ORDERSTEST
Your syntax looks OK. I use the Query Builder to build the query. Use the where condition editor and change the operator to Between and then add a colon and bind name.
My Query syntx looks like this.
SELECT ORDERSTEST.ORDER_ID, ORDERSTEST.ORDER_DATE, ORDERSTEST.CONTACT_ID
See attached.
When prompted for the bind values make sure and change the bind data type to DATE. When you do this you can then use a date picker to select the date.
See attached.
Debbie
Ohh ok, i didnt have the data type changed to DATE.
Now once i run the query with with the To_Date format and then change the dates to the prompt method i get two different results. What role does the Directions play in date selection in that pop up window?
My results are 735 vs 717 so its not a big difference but still… Message was edited by: oercan_859
EDIT: I figured it out. Just wanted to post incase anyone else has the same problem.
The time has to be 12:00 in both dates for it to return the same value, if the time is anyhting other than 12:00 it throws the values off. (if you have the time section on your dates that is)
Thank you!!
Message was edited by: oercan_859
