Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    11

    Unanswered: select from MyTable where date=?????

    I am having a huge problem with a select-statement. I am using an access 2002database and I am trying to get a query togeather from an asp-page that selects all records from a table where the date is today. I can't figure out how to solve it.

    ex. SELECT * FROM myTable WHERE columnDate = 'todays date'

    how do I do it? If anyone could help me I would soooo happy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    success will depend on what the datatype of columnDate is, and what sorts of values have been entered into it -- dates where the time component is zero, which you get from the date() function, or dates with a non-zero time component, which you get from the now() function
    Code:
    ... 
     where columnDate = date()
    or
    Code:
    ... 
     where columnDate >= date() 
       and columnDate < dateadd("d",1,date())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Posts
    11

    Thumbs up

    Thanx allot for that superfast answer, it was exactly what I needed. I really can't thank u enough.

  4. #4
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Example setting a date range based on posted fields:

    strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & Request.Form("MinDate") & "#) AND (TheDateField <= #" & Request.Form("MaxDate") & "#)"
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    paul, the upper end of that range will not include any datetimes from the max date if the column contains datetime values with time components

    you would have to either append 23:59:59 to maxdate or add 1 day to it and change <= to <
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    paul, the upper end of that range will not include any datetimes from the max date if the column contains datetime values with time components
    True, but I like date/time fields "where the time component is zero."

    And for times I like to use date/time fields where the date component is zero.
    Last edited by Bullschmidt; 02-03-04 at 08:46.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, don't tell me, let me guess...

    for applications where both date and time components are required (e.g. dental appointments), you use two datetime fields, one for the date with no time component, and one for the time with no date component, right? just so the sql is easier?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    no, don't tell me, let me guess...
    Correct, so that everything is more compartmentalized (including validating for proper date entry and in a separate field validating for proper time entry), and along with that the SQL is indeed easier.

    I would generally never force a user to enter a date AND a time all in the same field.
    Last edited by Bullschmidt; 02-03-04 at 09:38.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, no offence, but that's ugly

    how do you find the number of elapsed hours between two datetimes? if they can be separated by one or more days?

    in sql server, i would use

    select datediff(h,datetime2,datetime1)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Don't know that I ever put the difference in hours directly in a SQL statement (as opposed to perhaps calcuating in VBScript after the recordset has already been created), but if this works in a SQL statement:
    SELECT DateDiff('h', datetime2, datetime1)

    Then I would assume that perhaps this would work too:
    SELECT DateDiff('h', date2 + time2, date1 + time1)
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

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