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]]

Simon Gare wrote:

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

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
--
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"

[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]" <re******@NOyahoo.SPAMcomwrote in message
news:%2****************@TK2MSFTNGP06.phx.gbl...

Simon Gare wrote:

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

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
--
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"

[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 wrote:

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]" <re******@NOyahoo.SPAMcomwrote in message
news:%2****************@TK2MSFTNGP06.phx.gbl...

>Simon Gare wrote:

>>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

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
--
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"

--
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"

[Simon Gare]

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

Regards
Simon
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:ed**************@TK2MSFTNGP06.phx.gbl...

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 wrote:

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]" <re******@NOyahoo.SPAMcomwrote in message
news:%2****************@TK2MSFTNGP06.phx.gbl...

Simon Gare wrote:
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

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
--
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"

--
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"