Professional Web Applications Themes

Date field in DB - ASP

I am trying to compare the current date() with the date in an access db to pull out only records where the date is later than the todays date (so records where the date hasn't passed) Then I do this:- strSQL = "select * from tblEvents where enddate > " & date() & " order by startdate" it pulls me out every record even ones with the dat in the past. When I response.write the date() and objRS("enddate") to the screen the dates are correct/same format and the date() is older than the objRS("enddate") . So why is it pulling ...

  1. #1

    Default Date field in DB

    I am trying to compare the current date() with the date in an access db to
    pull out only records where the date is later than the todays date (so
    records where the date hasn't passed)

    Then I do this:-

    strSQL = "select * from tblEvents where enddate > " & date() & " order by
    startdate"

    it pulls me out every record even ones with the dat in the past.

    When I response.write the date() and objRS("enddate") to the screen the
    dates are correct/same format and the date() is older than the
    objRS("enddate") . So why is it pulling it out of the DB.

    I know a way to do it would be to pull all records out and do an if
    statement, but if I have, for example, 100 events 98 of which were expired,
    then it would be a waste of processing time to do the if and loop when I
    could pull them out from the DB from the start.

    Any ideas how I can et round this please?

    Cheers for anyones help.

    Stu


    Stuart Palmer Guest

  2. #2

    Default Re: Date field in DB

    If you did a:

    response.write strSQL
    response.End

    You'd probably get:

    select * from tblEvents where enddate > 7/31/2003 order by startdate

    So, what does that say? It says:

    Select * from tblEvents where EndDate is greater than (7 divided by 31
    divided by 2003).
    (At least that's what I believe is happening.)

    In Access, you need to delimit dates with the # sign, I believe. So, try:

    strSQL = "select * from tblEvents where enddate > #" & date() & "# order by
    startdate"

    Alternatively, you can use DATE() in your SQL query itself:

    Ray at work


    "Stuart Palmer" <tryandspammeyoucant.com> wrote in message
    news:bgb0na$ta0$1sp15at20.hursley.ibm.com...
    > I am trying to compare the current date() with the date in an access db to
    > pull out only records where the date is later than the todays date (so
    > records where the date hasn't passed)
    >
    > Then I do this:-
    >
    > strSQL = "select * from tblEvents where enddate > " & date() & " order by
    > startdate"
    >
    > it pulls me out every record even ones with the dat in the past.
    >

    Ray at Guest

  3. #3

    Default Re: Date field in DB

    Good observation Ray. I don't think I would have caught that... )

    -Andrew
    >If you did a:
    >
    >response.write strSQL
    >response.End
    >
    >You'd probably get:
    >
    >select * from tblEvents where enddate > 7/31/2003 >order by startdate
    >
    >So, what does that say? It says:
    >
    >Select * from tblEvents where EndDate is greater >than (7 divided by 31
    >divided by 2003).
    >(At least that's what I believe is happening.)
    >
    >In Access, you need to delimit dates with the # >sign, I believe. So,
    try:
    >
    >strSQL = "select * from tblEvents where enddate > >#" & date() & "#
    order by
    >startdate"
    >
    >Alternatively, you can use DATE() in your SQL >query itself:
    >
    >Ray at work
    * * * Sent via DevBuilder [url]http://www.devbuilder.org[/url] * * *
    Developer Resources for High End Developers.
    Andrew J Durstewitz Guest

  4. #4

    Default Re: Date field in DB

    Getting ed by something one time is to enough to make you remember. ;]

    Ray at work

    "Andrew J Durstewitz" <adurstewdevbuilder.org> wrote in message
    news:3f2916e3$0$195$75868355news.frii.net...
    > Good observation Ray. I don't think I would have caught that... )
    >
    > -Andrew
    >
    > >If you did a:
    > >
    > >response.write strSQL
    > >response.End
    > >
    > >You'd probably get:
    > >
    > >select * from tblEvents where enddate > 7/31/2003 >order by startdate
    > >
    > >So, what does that say? It says:
    > >
    > >Select * from tblEvents where EndDate is greater >than (7 divided by 31
    > >divided by 2003).
    > >(At least that's what I believe is happening.)
    > >

    Ray at Guest

  5. #5

    Default Re: Date field in DB

    In addition to Ray's suggestion, you can do this, instead:
    strSQL = "select * from tblEvents where enddate > date()" & _
    " order by startdate"

    JetSQL can a use the VBA date() function "internally". There is no need to
    pass the current date to the query.

    HTH,
    Bob Barrows

    PS. You should avoid "Select *" (selstar for short) in production code since
    it virtually guarantees that you will be pulling unnecessary data across the
    wire. Explicitly list the desired fields in your select statement.

    Stuart Palmer wrote:
    > I am trying to compare the current date() with the date in an access
    > db to pull out only records where the date is later than the todays
    > date (so records where the date hasn't passed)
    >
    > Then I do this:-
    >
    > strSQL = "select * from tblEvents where enddate > " & date() & "
    > order by startdate"
    >
    > it pulls me out every record even ones with the dat in the past.
    >
    > When I response.write the date() and objRS("enddate") to the screen
    > the dates are correct/same format and the date() is older than the
    > objRS("enddate") . So why is it pulling it out of the DB.
    >
    > I know a way to do it would be to pull all records out and do an if
    > statement, but if I have, for example, 100 events 98 of which were
    > expired, then it would be a waste of processing time to do the if and
    > loop when I could pull them out from the DB from the start.
    >
    > Any ideas how I can et round this please?
    >
    > Cheers for anyones help.
    >
    > Stu


    Bob Barrows Guest

  6. #6

    Default Re: Date field in DB

    Hey, that was my second suggestion, man! :P

    Ray at work

    "Bob Barrows" <reb_01501> wrote in message
    news:ObFzCr2VDHA.2228TK2MSFTNGP12.phx.gbl...
    > In addition to Ray's suggestion, you can do this, instead:
    > strSQL = "select * from tblEvents where enddate > date()" & _
    > " order by startdate"
    >
    > JetSQL can a use the VBA date() function "internally". There is no need to
    > pass the current date to the query.
    >

    Ray at Guest

  7. #7

    Default Re: Date field in DB

    Oh yeah!! Come on - reread your message: that was easy to miss wasn't it?
    :-)

    Ray at <%=sLocation%> wrote:
    > Hey, that was my second suggestion, man! :P
    >
    > Ray at work
    >
    > "Bob Barrows" <reb_01501> wrote in message
    > news:ObFzCr2VDHA.2228TK2MSFTNGP12.phx.gbl...
    >> In addition to Ray's suggestion, you can do this, instead:
    >> strSQL = "select * from tblEvents where enddate > date()" & _
    >> " order by startdate"
    >>
    >> JetSQL can a use the VBA date() function "internally". There is no
    >> need to pass the current date to the query.


    Bob Barrows Guest

Similar Threads

  1. Autofill date field
    By Dasman@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 1
    Last Post: December 1st, 09:10 PM
  2. Date Created field
    By Stephen_E_M_Jones@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 0
    Last Post: April 17th, 06:12 PM
  3. Converting a text field to a date field - FM6
    By Carl Mittler in forum FileMaker
    Replies: 2
    Last Post: October 17th, 09:38 PM
  4. Linking date field to text field entry
    By Trey in forum Microsoft Access
    Replies: 1
    Last Post: July 16th, 07:35 PM
  5. date in text field
    By Dwayne Epps in forum Adobe Dreamweaver & Contribute
    Replies: 0
    Last Post: July 9th, 09:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139