I have an interesting scenario:



I have a page where users can enter info into a database.  Each entry

contains a date.  Then, a manager can go to a search page and has the option

to view all entries between specified dates.  However, I'm having trouble

finding a query that pulls dates properly.



If anyone has a date query that works well please send me the query string?



I want to give the managers the option to select from_month, from_year and

to_month, to_year.



For the best possible solution how should I have the dates formatted?



Thank you,

Eli

Depends on your database.



If you're using Access, then grab the month/year



intYear & "/" & intMonth & "/01"



will give you the starting value



DateAdd("d", 1, dteEndingDate)



will give you the day after the last day of the end month.



Format your dates in ISO format (yyyy/mm/dd) and query the database using

BETWEEN



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Eli Schilling" <eschilli@t...>

To: "ASP Database Setup" <asp_database_setup@p...>

Sent: Sunday, October 14, 2001 6:37 PM

Subject: [asp_database_setup] Searching between 2 specified dates





: I have an interesting scenario:

:

: I have a page where users can enter info into a database.  Each entry

: contains a date.  Then, a manager can go to a search page and has the

option

: to view all entries between specified dates.  However, I'm having trouble

: finding a query that pulls dates properly.

:

: If anyone has a date query that works well please send me the query

string?

:

: I want to give the managers the option to select from_month, from_year and

: to_month, to_year.

:

: For the best possible solution how should I have the dates formatted?

:

: Thank you,

: Eli

:

:



ken@a...


$subst('Email.Unsub')

If :

fromDay = "01"

fromMonth = "Jan"

fromYear = "1999"



today = "31"

toMonth = "Dec"

toYear = "2001"



and if using access as database:

' make it #01-Jan-1999#

dateFrom = "#" & fromDay & "-" & fromMonth & "-" & fromYear & "#"

' make it #31-Dec-2001#

dateTo = "#" & toDay & "-" & toMonth & "-" & toYear & "#"



else if using oracle/sql server as database:

' make it '01-Jan-1999'

dateFrom = "'" & fromDay & "-" & fromMonth & "-" & fromYear & "'"

' make it '31-Dec-2001'

dateTo = "'" & toDay & "-" & toMonth & "-" & toYear & "'"



then, the SQL string can be:

sqlStr = "SELECT Employee_ID, FirstName, LastName, HireDate " & _

         "FROM Employees " & _

         "WHERE HireDate BETWEEN " & dateFrom & " AND " & dateTo





** no need to format your dates

*** it's best to provide a dropdown listbox (using HTML select) for the

parameters (dayFrom, monthFrom, yearFrom, etc..)



hope that helps,

Z.



In reply to:

---------------------------------------------

If anyone has a date query that works well please send me the query

string?



I want to give the managers the option to select from_month, from_year

and

to_month, to_year.



For the best possible solution how should I have the dates formatted?

---------------------------------------------