Retrieve date value issue

Discussion in 'ASP General' started by Simon Gare, Mar 3, 2007.

  1. Simon Gare

    Simon Gare Guest

    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
    Simon Gare, Mar 3, 2007
    #1
    1. Advertising

  2. 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"
    Bob Barrows [MVP], Mar 4, 2007
    #2
    1. Advertising

  3. Simon Gare

    Simon Gare Guest

    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:%...
    > 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, Mar 4, 2007
    #3
  4. 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:%...
    >> 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"
    Bob Barrows [MVP], Mar 4, 2007
    #4
  5. Simon Gare

    Simon Gare Guest

    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:...
    > 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:%...
    > >> 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, Mar 4, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    574
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,196
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    495
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    696
    Richard Heathfield
    Nov 8, 2003
  5. moonhk
    Replies:
    5
    Views:
    526
    moonhk
    Oct 11, 2006
Loading...

Share This Page