Webmaster Forum / ASP / Database Access / October 2008

It really depends on how the data is stored.

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.

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.

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" <greg@redseahookah.com>, haber iletisinde sunlari
yazdi:A7F9400E-81D7-443E-A9B7-DE0F354FDF50@microsoft.com...