473,886 Members | 2,368 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_for m.TimeOfBooking = DATEADD(day, -30, GetDate()) GROUP BY
dbo.booking_for m.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("Coun tOfTimeOfBookin g"))

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.booki ng_form.TimeOfB ooking)
AS CountOfTimeOfBo oking FROM dbo.booking_for m WHERE
dbo.booking_for m.allocated = 'Completed' AND dbo.booking_for m.CustomerID
='0' AND dbo.booking_for m.TimeOfBooking = DATEADD(day, -30, GetDate()) GROUP
BY dbo.booking_for m.TimeOfBooking ")

Regards

--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk
Mar 3 '07 #1
4 3175
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_for m.TimeOfBooking = DATEADD(day, -30, GetDate()) GROUP BY
dbo.booking_for m.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("Coun tOfTimeOfBookin g"))

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.booki ng_form.TimeOfB ooking) AS CountOfTimeOfBo oking FROM
dbo.booking_for m WHERE dbo.booking_for m.allocated = 'Completed' AND
dbo.booking_for m.CustomerID ='0' AND dbo.booking_for m.TimeOfBooking =
DATEADD(day, -30, GetDate()) GROUP BY dbo.booking_for m.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"
Mar 4 '07 #2
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.booki ng_form.TimeOfB ooking) AS CountOfTimeOfBo oking FROM
dbo.booking_for m WHERE dbo.booking_for m.TimeOfBooking = DATEADD(day, -1,
GetDate()) GROUP BY dbo.booking_for m.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******@NOyah oo.SPAMcomwrote in message
news:%2******** ********@TK2MSF TNGP06.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_for m.TimeOfBooking = DATEADD(day, -30, GetDate()) GROUP BY
dbo.booking_for m.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("Coun tOfTimeOfBookin g"))

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.booki ng_form.TimeOfB ooking) AS CountOfTimeOfBo oking FROM
dbo.booking_for m WHERE dbo.booking_for m.allocated = 'Completed' AND
dbo.booking_for m.CustomerID ='0' AND dbo.booking_for m.TimeOfBooking =
DATEADD(day, -30, GetDate()) GROUP BY dbo.booking_for m.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"


Mar 4 '07 #3
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,DAT EDIFF(d,0,getda te()),0)

Now modify that expression to calculate the date 30 days ago at midinight,
and 29 days ago at midnight:
SELECT DATEADD(day,DAT EDIFF(d,0,getda te())-30,0),
DATEADD(day,DAT EDIFF(d,0,getda te())-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,DAT EDIFF(d,0,getda te())-30,0) AND
TimeOfBooking < DATEADD(day,DAT EDIFF(d,0,getda te())-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.booki ng_form.TimeOfB ooking) AS CountOfTimeOfBo oking
FROM dbo.booking_for m WHERE dbo.booking_for m.TimeOfBooking =
DATEADD(day, -1, GetDate()) GROUP BY dbo.booking_for m.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******@NOyah oo.SPAMcomwrote in message
news:%2******** ********@TK2MSF TNGP06.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_f orm.TimeOfBooki ng = DATEADD(day, -30, GetDate()) GROUP
BY dbo.booking_for m.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("Co untOfTimeOfBook ing"))

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.boo king_form.TimeO fBooking) AS CountOfTimeOfBo oking FROM
dbo.booking_f orm WHERE dbo.booking_for m.allocated = 'Completed' AND
dbo.booking_f orm.CustomerID ='0' AND dbo.booking_for m.TimeOfBooking
= DATEADD(day, -30, GetDate()) GROUP BY
dbo.booking_f orm.TimeOfBooki ng")

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,
TimeOfBookin g 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"
Mar 4 '07 #4
Bob you're a star, that's been driving me crazy, thanks again.

Regards
Simon
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcomwrote in message
news:ed******** ******@TK2MSFTN GP06.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,DAT EDIFF(d,0,getda te()),0)

Now modify that expression to calculate the date 30 days ago at midinight,
and 29 days ago at midnight:
SELECT DATEADD(day,DAT EDIFF(d,0,getda te())-30,0),
DATEADD(day,DAT EDIFF(d,0,getda te())-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,DAT EDIFF(d,0,getda te())-30,0) AND
TimeOfBooking < DATEADD(day,DAT EDIFF(d,0,getda te())-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.booki ng_form.TimeOfB ooking) AS CountOfTimeOfBo oking
FROM dbo.booking_for m WHERE dbo.booking_for m.TimeOfBooking =
DATEADD(day, -1, GetDate()) GROUP BY dbo.booking_for m.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******@NOyah oo.SPAMcomwrote in message
news:%2******** ********@TK2MSF TNGP06.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_fo rm.TimeOfBookin g = DATEADD(day, -30, GetDate()) GROUP
BY dbo.booking_for m.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("Cou ntOfTimeOfBooki ng"))

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.book ing_form.TimeOf Booking) AS CountOfTimeOfBo oking FROM
dbo.booking_fo rm WHERE dbo.booking_for m.allocated = 'Completed' AND
dbo.booking_fo rm.CustomerID ='0' AND dbo.booking_for m.TimeOfBooking
= DATEADD(day, -30, GetDate()) GROUP BY
dbo.booking_fo rm.TimeOfBookin g")

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"


Mar 4 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
3021
by: Daniel Tonks | last post by:
First, please excuse the fact that I'm a complete MySQL newbie. My site used forum software that I wrote myself (in Perl) which, up until now, has used flat files. This worked fine, however lately I've been wanting to do more stuff with user accounts, and had been eying MySQL for over a year. Finally I've decided to start off small by converting the forum's account system to a MySQL database (and convert the rest later after I'm...
1
4137
by: Eugfene | last post by:
I have the following function in a html file: function selectEdit(fileID, processCode, processID, fileName,fileDesc) { document.forms.recordID.value = fileID; document.forms.processor.value = processCode; ` document.forms.processorID.value = processID; document.forms.interfaceFileName.value = fileName; document.forms.fileNameDesc.value=fileDesc; document.forms.submit();
4
3461
by: peashoe | last post by:
I have an asp page that uses a calendar.js (pop-up) file to add an exact date format in the text field (txtDDate). My problem is I need some javascript that sets an alert that does not allow them to select today. example: var dtToday = Date() if(document.frmSoftware.txtDDate.value == dtToday) { alert("You cannot select same day distributions. Please enter a new
2
5436
by: Rob | last post by:
Hi all, I've got multiple sets of radio button that are dynamically created in code and populated by a database query. The query returns about 20 recordsets with 3 radio buttons per recordset and I want to be able to retrieve the selected value from each of the sets of radio buttons.The names of each group of radio buttons is set by the database and I don't know how to retrieve the values because I don't know the names of these radio...
0
1694
by: george_Martinho | last post by:
It seems that the ASP.NET Microsoft team didn't think about this!! The profilemanager class has the following methods: - DeleteInactiveProfiles. Enables you to delete all profiles older than a specified date. - DeleteProfile. Enables you to delete a profile associated with a specified username. - DeleteProfiles. Enables you to delete a set of profiles. - FindInactiveProfilesByUserName. Returns a collection of ProfileInfo
2
1540
by: michele | last post by:
Hi, I want to load a listbox with the date that are stored in a database (mdb), the problem is when I retrieve the date it has a format like "01/01/2006 0.00.00". In Access the date is stored as short date. Thanks in advice
21
3399
by: Darin | last post by:
I have an applicatoin that works 100% perfect when running on a machine setup for English (United States), but when I change it to Spanish (Mexico), the dates start giving me fits. THe reason is USA is mm/dd/yyyy and mexico is dd/mm/yyyy. So, with the computer set to mexico, any standard CDATE function is going to return the date in the dd/mm/yyyy setting since that is what the computer is set to. I want to be able to enter a date in...
5
1958
by: giandeo | last post by:
Hello Experts. I am unable to retrieve records base on a date. Could you please help me. Here is my code for user to insert a date : <form name="hello" method="post" action="querytable.asp"> Please enter date (mm/dd/yyyy)<input type="text" name="adate">
7
2699
by: =?Utf-8?B?UVNJRGV2ZWxvcGVy?= | last post by:
I have a C# logging assembly with a static constructor and methods that is called from another C# Assembly that is used as a COM interface for a VB6 Application. Ideally I need to build a file name based on the name of the VB6 application. A second choice would be a file name based on the # COM interface assembly. I have tried calling Assembly.GetCallingAssembly() but this fails when I use the VB6 client. Is there a way to get this...
0
9956
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11172
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10872
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9592
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7987
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5808
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4627
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4235
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3245
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.