Hi all,

I have a little bit of a problem and can't seem to come up with an 
efficient solution.

I have a table that lists assests, comment, date, time. User fills in this 
data via form with the exception of the date & time, that is pulled when 
they enter or refresh the insert record page (AddComment.asp).

Now I want to have a results page that will pull ALL records for each 
assest in this order:
1) asset (ascending)
2) Date (descending)
3) Time (descending)

#3 is where the problem is: I just want to pull the records from the last 
12hrs, but sometimes that 12 hrs crosses midnite (6pm-6am & 6am-6pm 
previous day). The data I want will always be from 6-6
ex: records from 6am 10/25/02 to 6pm 10/24/02 OR 6pm 10/24/02 to 6am 
10/24/02.

Is SQL statement the best way to do this or should I pull all the records 
from a range and use ASP to do calulation and display records. I think the 
SQL is more effiecient for my purposes due to the limited resources on the 
client but the server has plenty.

MS IIS 5.0
MS Access 2K

TIA for any help. I would greatly appreciate code and detail explanation, 
fairly new to ASP.

Brett

The question I'd ask is why are you storing the date and time separately. It
seems clear that the attribute of the entity is "date plus time", and, and
you can see, storing them separately starts to give you problems trying to
get the data back out!

You can do it like this:

SELECT
    Asset,
    AssetDate,
    AssetTime
FROM
    Assets
WHERE
    AssetDate + AssetTime
        BETWEEN #2002/10/28 6:00 PM# AND #2002/10/29 6:00 AM#
ORDER BY
    Asset DESC, AssetDate DESC, AssetTime DESC

You correct in saying that doing it in SQL will be much more efficient then
using a cursor in ASP to loop through records processing them.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Brett Sherman" <onrampid@e...>
Subject: [asp_web_howto] SQL statement for time range pull


: Hi all,
:
: I have a little bit of a problem and can't seem to come up with an
: efficient solution.
:
: I have a table that lists assests, comment, date, time. User fills in this
: data via form with the exception of the date & time, that is pulled when
: they enter or refresh the insert record page (AddComment.asp).
:
: Now I want to have a results page that will pull ALL records for each
: assest in this order:
: 1) asset (ascending)
: 2) Date (descending)
: 3) Time (descending)
:
: #3 is where the problem is: I just want to pull the records from the last
: 12hrs, but sometimes that 12 hrs crosses midnite (6pm-6am & 6am-6pm
: previous day). The data I want will always be from 6-6
: ex: records from 6am 10/25/02 to 6pm 10/24/02 OR 6pm 10/24/02 to 6am
: 10/24/02.
:
: Is SQL statement the best way to do this or should I pull all the records
: from a range and use ASP to do calulation and display records. I think the
: SQL is more effiecient for my purposes due to the limited resources on the
: client but the server has plenty.
:
: MS IIS 5.0
: MS Access 2K
:
: TIA for any help. I would greatly appreciate code and detail explanation,
: fairly new to ASP.

To answer your question: I kept getting a syntax error when I wasn't 
storing them seperately. I tried using "<%= DATE%>" hoping that it would 
pull date/time and insert it in the table properly, but I guess Access has 
a wierd format structure. I tried probably 10 different things and still 
would get syntax errors, so I did them separately and it worked.

How would I modify the code below so that at the time the results page is 
accessed that it will use the current date/time by default and then go 
back to the previous 6 o'clock hr (whether is be 6am or 6pm, even if it 
crosses midnite?)

Thx for your response Ken..

Brett

> The question I'd ask is why are you storing the date and time 
separately. It
seems clear that the attribute of the entity is "date plus time", and, and
you can see, storing them separately starts to give you problems trying to
get the data back out!

You can do it like this:

SELECT
    Asset,
    AssetDate,
    AssetTime
FROM
    Assets
WHERE
    AssetDate + AssetTime
        BETWEEN #2002/10/28 6:00 PM# AND #2002/10/29 6:00 AM#
ORDER BY
    Asset DESC, AssetDate DESC, AssetTime DESC

You correct in saying that doing it in SQL will be much more efficient then
using a cursor in ASP to loop through records processing them.

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Brett Sherman" <onrampid@e...>
Subject: [asp_web_howto] SQL statement for time range pull

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Brett Sherman" <onrampid@e...>
Subject: [asp_web_howto] Re: SQL statement for time range pull


: To answer your question: I kept getting a syntax error when I wasn't
: storing them seperately. I tried using "<%= DATE%>" hoping that it would
: pull date/time and insert it in the table properly, but I guess Access has
: a wierd format structure. I tried probably 10 different things and still
: would get syntax errors, so I did them separately and it worked.

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

Maybe you should go back to try and work out why you were getting your
errors, and solving that problem. Having a properly designed database means
you wont have a whole heap of problems later on (trust me).

www.adopenstatic.com/faq/dateswithaccess.asp

addresses the most common problem using Access and Dates.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: How would I modify the code below so that at the time the results page is
: accessed that it will use the current date/time by default and then go
: back to the previous 6 o'clock hr (whether is be 6am or 6pm, even if it
: crosses midnite?)

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

You would use a Select Case (of If...Then construct) statement in your ASP
script to work out the previous 6 o'clock. You would then use DateAdd to
subtract 12 hours from it. You would then put these two values into your SQL
statement, and send it to the database.

<%
Dim dtePrevious6oclock

Select Case True
Case Hour(Now()) < 6 then
    ' Need to go back to 6pm yesterday
    dtePrevious6oclock = DateAdd("h", 18, DateAdd("d", -1, Date()))
Case Hour(Now()) < 18 then
    ' Need to go to 6am
    dtePrevious6oclock = DateAdd("h", 6, Date())
Case Else
    dtePrevious6oclock = DateAdd("h", 18, Date())
End Select

' Now we have the previous 6 oclock, add 12 hours to it
dteEndingTime = DateAdd("h", 12, dtePrevious6oclock

' Now we need to format the date in ISO format
' www.adopenstatic.com/faq/dateswithaccess.asp
' www.adopenstatic.com/resources/code/formatdate.asp

' Now stick those values into an SQL statement

' Now generate recordset

Cheers
Ken