Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ASP/SQL/Access DB - compare dates

    I'm a novice to ASP programming. I have an Microsoft Access DB that has a date field (e.g. 8/9/2005) for each record. I want to write an SQL statement that only returns records for the past 7 days. I am accustom to written statements for DB2 access, such as:

    select * from mytable where date(mydate) > date(current timestamp - 7 days)

    [or something similar]

    Can anyone help me here? Thank you.

  • #2
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Hi, and welcome to the board!

    1) Example as you requested:

    BeginDate = DateAdd("D", -7, Date())
    EndDate = Date()
    strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & BeginDate & "#) AND (TheDateField <= #" & EndDate & "#)"

    2) Example setting a date range based on posted fields:

    strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & Request.Form("MinDate") & "#) AND (TheDateField <= #" & Request.Form("MaxDate") & "#)"

    3) Example setting a date range to be the current and previous months:

    strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & DateSerial(Year(Date()), Month(Date()) + 1, 0) & "#) AND (TheDateField <= #" & DateSerial(Year(Date()), Month(Date()) - 1, 1) & "#)"
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic 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
    •