Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

need help with input dates 1

Status
Not open for further replies.
bookouri

bookouri

IS-IT--Management
Feb 23, 2000
1,464
US
I have used this .asp to input search fields before but it was alway for text data. I cant get the right combination to do a search on a date range. Can anybody tell me where im going wrong?

--- i have a couple of input boxes to get a begin date and ----end date

<body onload=""document.searcher.search.focus()"> <p class="style17">  </p>

<form name="searcher action="<%=strURL %>" method=""get">
<div class="style18">
<span class="style17"">Begin Date:</span><input name="search_begin" value="<%=strsearch_begin %>" size="20" />
</div>
<center>
<div class="style18">
<span class="style17">End Date: </span>
<input name="search_end" "value="<%=strsearch_end %>" size="20" />
<input type="submit" />
</div>
</form>
<p class="style19"> [Enter Begin Date and End Date in format: (00/00/0000)]</p>


-- then i plug that input into my sql_str like this:
sql_str = "select count(det_placed)from gctab where det_placed = 'Y' and to_char(det_date,'mm/dd/yyyy') >= '" & (replace(strsearch_begin, "'", "''")) & "' "
set rs = myconnection.execute(sql_str)
det= rs(0)
rs.close

can anybody point me in the right direction to manage date's in this kind of situation?




 
Sort by date Sort by votes
What type of database are you using? Oracle, SQL Server, Access, MySQL, etc...

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
i usually start such a SQL statement in the DBMS tool. This sounds like Oracle (to_char funtion). So i would start PL/SQL and test the complete statement.

Then i copy/paste it into the script.

Normally it is a concatenation of text/ fields, so the first time i response.write sql_str to screen, and inspect if it looks like the one that tested oke.

In fact: i copy/paste it back into the DBMS tool, and run it.

If the filed is "date" type in the database i suggest that you to a to_date() with the entered values, and not a to_char() with the date field.
 
Upvote 0 Downvote
its coming from oracle. I dont have any trouble formatting the select statement, what im having trouble with is handling the date input boxes...

I have got the input boxes working except for one problem.
I am changing the date in my select to a char... to_char(mydate,'mm/dd/yyyy') and comparing it with the input date:
to_char(det_date,'mm/dd/yyyy') >= '" & (replace(strsearch_begin, "'", "''")) ... i now have this working as long as the user puts in their date with all the numbers.. like 01/01/1999.. if they put in 1/1/1999 my comparison fails.. so at this point i guess my best bet is to figure out how to format the users input variable "strsearch" so i get a good date from them...

 
Upvote 0 Downvote
3 fields for dd, mm and yyyy? and concat the result to desired format?
 
Upvote 0 Downvote
I have figured out how to check for a valid date being entered, and I have figured out how to break that date down into month, day and year components. The problem I havnt been able to solve is rebuilding those components into a format I can use for my oracle date. To get my oracle statement to work right, I have to pass it either 19990101 or 01-JAN-1999. Even though I have an asp case statement that takes the month date part and makes it = "01" for january, when i reassemble the date asp drops off all my "0"s. target_mo = "01" doesnt become "01" but just "1". How can I make mo = "01", day = "01" and year= "1999" and turn them into 19990101 or 01011999? When i concat the year & mo & day asp gives me 199911 for example. How can i retain those zeros?

 
Upvote 0 Downvote
eg like this?

Code: 
dim dd,mm,yyyy
dd = 1
mm = 4
yyyy = 2010

cTheDate = right("00" & dd,2) & right("00" & mm ,2) & yyyy

response.write cTheDate
 
Upvote 0 Downvote
How about using a Jquery date picker or some other tool to control how the date is entered?

Otherwise, I would agree with foxbox on his padding method.

Additionally, as long as you can be sure of the format of the date going in, I would just use a to_date('04/01/2010,'MM/DD/YYYY'), (you can adjust to mm-dd-yyyy, or mmddyyyy, etc) and change the query to something like this:

sql_str = "select count(det_placed)from gctab where det_placed = 'Y' and det_date >= to_date('" & (replace(strsearch_begin, "'", "''")) & "','MM/DD/YYYY') "

This is just in lieu of treating a date as a char and letting Oracle convert them implicitly for the comparison or comparing them alphabetically. (I'm not sure what Oracle does... MS SQL guy here). We have to talk to an Oracle db (openquery through SQL Server stored procs) and use the above method without fail.

Good luck!
Oracle to_date tips
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
299
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
273
leifoet
leifoet
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
423
leifoet
leifoet
JScannell
  • Locked
  • Question
Border issue with Google Chrome on an Android phone
Replies
0
Views
323
JScannell
JScannell
penguinspeaks
  • Locked
  • Question
write HTML code within ASP line
Replies
1
Views
442
xwb
xwb

Part and Inventory Search

Sponsor

Back
Top