Date Comparison

Discussion in 'ASP General' started by Dino, Jan 2, 2005.

  1. Dino

    Dino Guest

    I am creating a website that is going to ask the user to enter a date! then
    from an access database get all records where a date field is greater then
    the date entered! Sounds simple, I do it in vb all the time!

    here is my sql line: Select * From Participants WHERE DateJoined > 01/14/07
    ORDER BY TournamentID

    it updates fine with the date the user has entered! I know in vb I need to
    put ## around the date I am comparing! I tried that, I tried %%. still I
    am getting all records from the database!

    any and all help is appreciated!
    thank you in advance.

    mike
     
    Dino, Jan 2, 2005
    #1
    1. Advertising

  2. Dino wrote:
    > I am creating a website that is going to ask the user to enter a
    > date! then from an access database get all records where a date
    > field is greater then the date entered! Sounds simple, I do it in vb
    > all the time!
    >
    > here is my sql line: Select *


    http://www.aspfaq.com/show.asp?id=2096


    > From Participants WHERE DateJoined >
    > 01/14/07 ORDER BY TournamentID
    >
    > it updates fine with the date the user has entered! I know in vb I
    > need to put ## around the date I am comparing! I tried that, I tried
    > %%. still I am getting all records from the database!
    >


    It's ##. Why would you try %%? That's for strings, not dates, and it's not
    even a delimiter. It's a wildcard. Of course, if you used parameters, you
    would not have to worry about these silly delimiter issues.
    (http://www.google.com/groups?selm=&oe=UTF-8&output=gplain
    )

    Why are you using an ambiguous format for your date? Always pass dates in
    yyyymmdd format. For Access, you will need to use #yyyy-mm-dd#

    http://www.aspfaq.com/show.asp?id=2040 help with dates
    http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion
    http://www.aspfaq.com/show.asp?id=2313 vbscript

    The only way to debug a sql statement is to find out what the statement is
    that you are passing to the database. The only way to determine that with
    Access is to Response.Write it, like this:

    dim dDate, sSQL
    dDate=<wherever you are getting the date from>
    sSQL = Select * From Participants WHERE DateJoined > #" & _
    dDate & "# ORDER BY TournamentID"

    '*****for debugging - comment these lines when finished***
    Response.Write sSQL
    Response.End
    '******************************************************************

    Run the page. If you've created the sal statement correctly, you should be
    able to open your database in Access, create a new query in design view
    without selecting any tables, switch to SQL View, paste in the statement
    that appears in the browser window and run it without modification (that is
    your goal when creating dynamic sql: create a statement that will run
    without modification in the query execution tool provided by your database.)

    The above should be done before you open a connection to your database.

    Bob Barrows
    --
    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], Jan 2, 2005
    #2
    1. Advertising

  3. Dino wrote:
    > I am creating a website that is going to ask the user to enter a
    > date! then from an access database get all records where a date
    > field is greater then the date entered!

    ..
    There was no need to crosspost this here. Your question is definitely
    database-related, and most of us subscribe to both of these asp newsgroups,
    so you did not increase your chances of getting an answer.

    --
    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], Jan 2, 2005
    #3
  4. Dino

    Dino Guest

    Re: Date Comparison - thanks bob - question answered

    I wish to take this time to thank you Bob, for you valuable input. your
    ideas and comments will be useful as I move forward! thank You

    "Bob Barrows [MVP]" <> wrote in message
    news:%23b$...
    > Dino wrote:
    > > I am creating a website that is going to ask the user to enter a
    > > date! then from an access database get all records where a date
    > > field is greater then the date entered! Sounds simple, I do it in vb
    > > all the time!
    > >
    > > here is my sql line: Select *

    >
    > http://www.aspfaq.com/show.asp?id=2096
    >
    >
    > > From Participants WHERE DateJoined >
    > > 01/14/07 ORDER BY TournamentID
    > >
    > > it updates fine with the date the user has entered! I know in vb I
    > > need to put ## around the date I am comparing! I tried that, I tried
    > > %%. still I am getting all records from the database!
    > >

    >
    > It's ##. Why would you try %%? That's for strings, not dates, and it's not
    > even a delimiter. It's a wildcard. Of course, if you used parameters, you
    > would not have to worry about these silly delimiter issues.
    >

    (http://www.google.com/groups?selm=&o
    e=UTF-8&output=gplain
    > )
    >
    > Why are you using an ambiguous format for your date? Always pass dates in
    > yyyymmdd format. For Access, you will need to use #yyyy-mm-dd#
    >
    > http://www.aspfaq.com/show.asp?id=2040 help with dates
    > http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion
    > http://www.aspfaq.com/show.asp?id=2313 vbscript
    >
    > The only way to debug a sql statement is to find out what the statement is
    > that you are passing to the database. The only way to determine that with
    > Access is to Response.Write it, like this:
    >
    > dim dDate, sSQL
    > dDate=<wherever you are getting the date from>
    > sSQL = Select * From Participants WHERE DateJoined > #" & _
    > dDate & "# ORDER BY TournamentID"
    >
    > '*****for debugging - comment these lines when finished***
    > Response.Write sSQL
    > Response.End
    > '******************************************************************
    >
    > Run the page. If you've created the sal statement correctly, you should be
    > able to open your database in Access, create a new query in design view
    > without selecting any tables, switch to SQL View, paste in the statement
    > that appears in the browser window and run it without modification (that

    is
    > your goal when creating dynamic sql: create a statement that will run
    > without modification in the query execution tool provided by your

    database.)
    >
    > The above should be done before you open a connection to your database.
    >
    > Bob Barrows
    > --
    > 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"
    >
    >
     
    Dino, Jan 2, 2005
    #4
    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:
    619
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,301
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    534
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    735
    Richard Heathfield
    Nov 8, 2003
  5. Deepu
    Replies:
    1
    Views:
    250
    ccc31807
    Feb 7, 2011
Loading...

Share This Page