Retrieve date value issue

[Simon Gare]

Hi all,

I am trying to retrieve a count of booking entries made 30 days ago, below
is the end of the query I am having problems with.

dbo.booking_form.TimeOfBooking = DATEADD(day, -30, GetDate()) GROUP BY
dbo.booking_form.TimeOfBooking")

When I use the = sign the error reads

Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record.

for the entry on the page

(rsCash30("CountOfTimeOfBooking"))

But I know the record exists, also the query works when I use the signs < or

or <> all works ok but not with the = ??

Full SQL query reads

Set rsCash30 = myConn.Execute("SELECT Count(dbo.booking_form.TimeOfBooking)
AS CountOfTimeOfBooking FROM dbo.booking_form WHERE
dbo.booking_form.allocated = 'Completed' AND dbo.booking_form.CustomerID
='0' AND dbo.booking_form.TimeOfBooking = DATEADD(day, -30, GetDate()) GROUP
BY dbo.booking_form.TimeOfBooking")

Regards

--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk

 

[Bob Barrows [MVP]]

Hi all,

I am trying to retrieve a count of booking entries made 30 days ago,
below is the end of the query I am having problems with.

dbo.booking_form.TimeOfBooking = DATEADD(day, -30, GetDate()) GROUP BY
dbo.booking_form.TimeOfBooking")

When I use the = sign the error reads

Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

for the entry on the page

(rsCash30("CountOfTimeOfBooking"))

But I know the record exists, also the query works when I use the
signs < or

Full SQL query reads

Set rsCash30 = myConn.Execute("SELECT
Count(dbo.booking_form.TimeOfBooking) AS CountOfTimeOfBooking FROM
dbo.booking_form WHERE dbo.booking_form.allocated = 'Completed' AND
dbo.booking_form.CustomerID ='0' AND dbo.booking_form.TimeOfBooking =
DATEADD(day, -30, GetDate()) GROUP BY dbo.booking_form.TimeOfBooking")

Regards

I am assuming SQL Server due to the presence of "dbo" in your sql statement.
Please don't make us guess. Always reveal the database type and version when
asking database-related questions.

You need to remember that the datetime datatype stores BOTH time AND date.
They are stored as paired integers: the firs integer contains the number of
days since the seed date, and the second contains the number of milliseconds
since midnight.

So, to see your problem, do this in QA or SSMS:

SELECT Top 10 DATEADD(day, -30, GetDate()) CompareDate,
TimeOfBooking FROM booking_form

Even this small sample should reveal that is is unlikely that booking_form
will ever be EQUAL to DATEADD(day, -30, GetDate()).

I am not sure what you are storing in TimeOfBooking. The name suggests that
you are attempting to store time only. I am not going to waste time
providing what may be an inappropriate solution based on a guess. Tell us
the datatype of TimeOfBooking and show us a few examples of the data it
contains.

Bob Barrorws

 

[Simon Gare]

Sorry Bob,

Time of Booking contains Date and Time of vehicle required eg 21/1/2007
9:40:00. The query I am trying to build retrieves a count of the bookings
entered 30 days ago hence the

(SELECT Count(dbo.booking_form.TimeOfBooking) AS CountOfTimeOfBooking FROM
dbo.booking_form WHERE dbo.booking_form.TimeOfBooking = DATEADD(day, -1,
GetDate()) GROUP BY dbo.booking_form.TimeOfBooking")

Obviously the problem is as you described that the stored value is date +
time, can you sugeest an alternative?

Regards
Simon

 

[Bob Barrows [MVP]]

You want all records containing that date, regardless of time? First, you
need to calculate today's date at midnight. Here is one way to do that (0
can be used to represent the seed date):

SELECT DATEADD(day,DATEDIFF(d,0,getdate()),0)

Now modify that expression to calculate the date 30 days ago at midinight,
and 29 days ago at midnight:
SELECT DATEADD(day,DATEDIFF(d,0,getdate())-30,0),
DATEADD(day,DATEDIFF(d,0,getdate())-29,0)

Now use those expressions in your Where clause to get all records between
those two dates, excluding the later date, like this:

WHERE TimeOfBooking >=
DATEADD(day,DATEDIFF(d,0,getdate())-30,0) AND
TimeOfBooking < DATEADD(day,DATEDIFF(d,0,getdate())-29,0)

Bob

 

[Simon Gare]

Bob you're a star, that's been driving me crazy, thanks again.

Regards
Simon