Toad World® Forums

How do I prompt a Date input?


#1

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…


#2

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:


#3

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


#4

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?


#5

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
usingbinds2.png


#6

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
usingbinds1.png


#7

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