Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 29th, 2004, 12:05 AM
SoC SoC is offline
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default How do you search datetime field?

Hi,

Posted this in another forum - not sure if it was the right
one, so here it is again.

I have a SQL database table with a datetime field in dd/mm/yyyy
format.

I have a web page that allows users to search this database.

How do you search a datetime field?

i.e., I want users to be able to enter a date into a text
field that will search the database.

I know it's not as simple as saying SELECT * FROM theTABLE WHERE
dateField = theTextFieldVariable.

Any help much appreciated

S


 
Old October 29th, 2004, 12:38 AM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

Yes its almost as simple as that:

sql = "SELECT fieldName, anotherFieldName FROM tableName WHERE dateFieldName >= '" & request.form("dateFrom") & "' AND dateFiedName <= '" & request.form("dateTo") & "';"

Wind is your friend
Matt
 
Old October 29th, 2004, 01:15 AM
SoC SoC is offline
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Matt, that does work!

Actually, what I have now started trying to do is have one drop down box for the month, and one for the year.
Users select the month and year, and this returns all records matching these criteria.

Does anyone know how to do this?

cheers
S

 
Old October 29th, 2004, 04:02 AM
Friend of Wrox
Points: 410, Level: 7
Points: 410, Level: 7 Points: 410, Level: 7 Points: 410, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2004
Location: New Delhi, India.
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to anubhav.kumar
Default

Greetings to all
Dear S (SoC)
use month and year functions in SQL Query to match values of your combo boxes

Hope this solves what you want

Anubhav Kumar
 
Old October 29th, 2004, 06:01 PM
Friend of Wrox
Points: 6,664, Level: 34
Points: 6,664, Level: 34 Points: 6,664, Level: 34 Points: 6,664, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2004
Location: Sydney, NSW, Australia.
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

Soc

I use a javascript date picker, there are plenty of free ones out there. You say they pick a month and a year, where does the day come from? If they are also picking a day I would use the pop up calendar method otherwise when they pick a month with 30 days in it you have to disable 31 from the list - then you have leap years .....

In saying this if they just pick a month and a year, yes the way you have suggested will work. You can still use the query in my prevuious post:
1..Replace request.form("dateFrom") and Request.form("dateTo") with variables, lets say 'dFrom' and 'dTo'
2..Pass your form values (month and year)
3..Now build your from date and to date variables:
dFrom = "1/" & request.form("monthSelection") & "/" & request.form("yearSelection")
dTo = "1/" & request.form("2ndMonthSelection") & "/" & request.form("2ndYearSelection")

The query changes to:
sql = "SELECT fieldName, anotherFieldName FROM tableName WHERE dateFieldName >= '" & dFrom & "' AND dateFiedName <= '" & dTo & "';"


Wind is your friend
Matt
 
Old October 29th, 2004, 10:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi SoC,

Take a look at this thread, that was discussed on similar requirement.

ASP - Drop Down Box with Date

Look for my first post that contains code, that should help you out in this.

Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old October 31st, 2004, 09:39 PM
SoC SoC is offline
Authorized User
 
Join Date: Jul 2004
Location: , , .
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks very much everyone,

I ended up trying happygv's suggestion. It worked well, except I changed the SQL slightly so that if the user didn't make a selection, it would return all results. There may be a more efficient way of doing this, but it seems to work by using LIKE instead of = and adding wildcard % before and after the month and year variables.

Cheers
S






Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Use DateTime Field kcsathish ASP.NET 2.0 Professional 0 July 15th, 2008 01:01 AM
Datetime Field as index daworm MySQL 2 September 17th, 2006 11:33 PM
Text to DateTime Field lenredles SQL Server 2000 1 March 6th, 2006 10:09 PM
Searching datetime field SoC Classic ASP Databases 2 October 29th, 2004 10:38 PM
Query DateTime Field mtalam Access 5 June 3rd, 2004 06:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.