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

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

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

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

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

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

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