Retrieve date value issue

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

UALRe: Retrieve date value issue

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"

Re: UALRe: Retrieve date value issue

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]" wrote in message
news:%23S7lSilXHHA.4396@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"
>
>

Re: UALRe: Retrieve date value issue

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]" wrote in message
> news:%23S7lSilXHHA.4396@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"

Re: UALRe: Retrieve date value issue

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

Regards
Simon
"Bob Barrows [MVP]" wrote in message
news:edh3XooXHHA.4396@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]" wrote in message
> > news:%23S7lSilXHHA.4396@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"
>
>