Select Records between Date/Time & Date/Time?

[Greg]

I've built a Point-of-Sale using "classic" asp (it's what I know) and I'm
trying to get daily sales totals. The problem is sales for the day do not
stop at 11:59pm. What is a clean way to select the records from say 8:00am
at start of business to 4:00am the next morning? I should be able to figure
this out, but I think I've been looking at this too long...

Any help would be appreciated!

Thanks,
Greg

[Bob Barrows [MVP]]

It really depends on how the data is stored.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Greg]

"Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
news:OWvVrmg...@TK2MSFTNGP03.phx.gbl...

The date/time stamps are stored as such.

[Greg]

"Greg" <gr...@redseahookah.com> wrote in message
news:1107D1CE-324D-4F39...@microsoft.com...

Seperately that is...

[Bob Barrows [MVP]]

Greg wrote:
> "Greg" <gr...@redseahookah.com> wrote in message
> news:1107D1CE-324D-4F39...@microsoft.com...
>>
>> "Bob Barrows [MVP]" <reb0...@NOyahoo.SPAMcom> wrote in message
>> news:OWvVrmg...@TK2MSFTNGP03.phx.gbl...
>>> Greg wrote:
>>>> I've built a Point-of-Sale using "classic" asp (it's what I know)
>>>> and I'm trying to get daily sales totals. The problem is sales
>>>> for the day do not stop at 11:59pm. What is a clean way to select
>>>> the records from say 8:00am at start of business to 4:00am the next
>>>> morning? I should be able to figure this out, but I think I've
>>>> been looking at this too long...
>>>>
>>>> Any help would be appreciated!
>>>>
>>> It really depends on how the data is stored.
>>>
>>>
>>>
>>

>> The date/time stamps are stored as such.
>>
>
> Seperately that is...

More details please:
database type and version
table name
fieldnames and datatypes

It helps to include a few rows of sample data followed by desired results

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Old Pedant]

"Greg" wrote:

> I've built a Point-of-Sale using "classic" asp (it's what I know) and I'm
> trying to get daily sales totals. The problem is sales for the day do not
> stop at 11:59pm. What is a clean way to select the records from say 8:00am
> at start of business to 4:00am the next morning? I should be able to figure
> this out, but I think I've been looking at this too long...

You ever figure this out??? It's not hard, but you goofed by putting the
data and time into separate fields in the DB. Now you just have to lump them
back together.

And what do you do about times between 4AM and 8AM????

Yeah, I know...the store is closed. Want to be that someday somebody sells
something at 4:03 AM??

Anyway, fortunately in Access you can just add a time to a date to get a
date/time value. And I'm going to assume that your "days" really run from
8AM to 7:59:59 AM.

SO...

SELECT DATEVALUE(sales_date+sales_time-1/3) AS theDate, SUM(sales_amount) AS
total
FROM sales_table
GROUP BY DATEVALUE(sales_date+sales_time-1/3)
ORDER BY 1

Now, that's a hacky trick: In Access, dates and times are represented as
the number of days since 12/30/1899. Dates are the whole integer number of
days. Times are the fractional part of a day. So when I do
-1/3
I am subtracting 1/3 of a day--which is 8 hours--from the sum of the date
and time. That is, I am adjusting the effective date *backwards* by 8 hours.

If this really offends you sensibilities, you can use DATEADD instead:

DATEVALUE( DATEADD( 'h', -8, sales_date + sales_time ) )

But next time, don't store the date and time in separate fields in the DB.
In any DB but Access, you'd make the whole thing a lot harder.

[KEN]

I like to use the DataDiff function in SQL

SELECT * FROM table WHERE the_time_of_sale = (SELECT Max(time_of_sale)
from table WHERE DATEDIFF(hh,the_time_of_sale, '" &
rsFirstOfSession("date or whatever") & "' ) > -27AND
DATEDIFF(hh,the_time_of_sale, '" & rsFirstOfSession("date or
whatever") & "' ) > -3);"'

So that would give you everything not more than 27 before hours from
the date entered and not more than 3 hours ahead.

And that first of session could be today's date or user input
whatever.

[Seyfullah IZMIRLI]

If i were you, i'll use DateTime as Float...
for example.

SELECT <NEEDED FIELDS>
FROM <TABLE>
WHERE <FILTEREDDATE> > CAST(39732,39990 AS DATETIME)

with this case i would use the limitation as seconds

Have fun with SQL

Seyfullah IZMIRLI

"Greg" <gr...@redseahookah.com>, haber iletisinde sunlari
yazdi:A7F9400E-81D7-443E...@microsoft.com...