Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Unanswered: Need to return records for ONLY the past 30 days...

    I'm using Dreamweaver MX on Win2000: ASP 2.0

    I have a table and I need to query to return the records for the past 30 days.

    The table is Timesheets.
    The date field is DateTaught. The date is stored as mm/dd/yyyy.

    Here's the code output by Dreamweaver:

    RecsetTimesheets.Source = "SELECT * FROM Timesheets WHERE CustomerListID = '" + Replace(RecsetTimesheets__MMColParam, "'", "''") + "' ORDER BY DateTaught DESC"

    I just want the records that fall within the past 30 days.

    Can you help?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What database are you using ?

  3. #3
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    DB type...

    Man, how did I miss including that?

    ACCESS 2000.

    Thanks.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    You can use the following to query dates (I am using northwind as an example):

    select * from orders where orderdate between #8/1/1994# and #8/30/1994#

  5. #5
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    Return records a set number of days ago...

    Thanks, but that's not quite what I need. I'm going to move this over the the ACCESS forum...

  6. #6
    Join Date
    Nov 2002
    Location
    Houston, Texas
    Posts
    85

    One more try...

    Well, I'll try one more time.

    I'm using Dreamweaver MX to generate ASP. I'm connecting to an ACCESS DB. This is my SQL string:

    --------------------------------------------
    RecsetTimesheets.Source = "SELECT * FROM Timesheets WHERE CustomerListID = '" + Replace(RecsetTimesheets__MMColParam, "'", "''") + "' AND ORDER BY DateTaught ASC"
    -------------------------------------------
    *** NOTE ***
    This takes the parameter passed from the URL:
    " + Replace(RecsetTimesheets__MMColParam, "'", "''") + "
    ************

    I want to filter the date by DateTaught

    I have a variable from another table that tells me how many days to display at a time:
    (RecsetCustomer.Fields.Item("LessonDisplay").Value )

    This variable is a simple number. I want to take the current date, then subtract my variable from it, and then show all DateTaught rows that are greater than it. Something like this forula:

    Show all records where DateTaught >= (TodaysDate - dayVariable)

    I could write this in ASP or Javascript, but how do I do it in SQL?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE DATEDIFF("d",DateTaught,date()) < dayVariable


    rudy
    http://rudy.ca/

Posting Permissions

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