Results 1 to 5 of 5

Thread: Date Formula

  1. #1
    Join Date
    Jan 2011
    Posts
    83

    Unanswered: Date Formula

    I am wanting to run a query with a strange criteria. The query returns a list of invoices. If today is 16th June, I want the query to return all invoices dated 30th April and before.

    When we get to July, I want the query to return all invoices dated 31st May and before.

    How would I do this in the criteria of a query?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    start with today's date -- DATE()

    then find the day of the month and subtract 1 -- DAY(DATE())-1

    subtract that number of days from today's date -- DATEADD("D",-(DAY(DATE())-1),DATE())

    subtract one more month -- DATEADD("M",-1,DATEADD("D",-(DAY(DATE())-1),DATE()))

    you now have a date for the first day of the previous month

    example: since we're in june, this gives you the 1st day of may

    then just write your query to accept all dates that are earlier than that
    Code:
    WHERE datecolumn < DATEADD("M",-1,DATEADD("D",-(DAY(DATE())-1),DATE()))
    note that this formula works perfectly well going backwards over year boundaries, too

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

  3. #3
    Join Date
    Jan 2011
    Posts
    83
    Thank you for this.

    How would I use this in the criterea field in my query design view?

  4. #4
    Join Date
    Jan 2011
    Posts
    83
    Ignore me, it works a dream. Thank you.

    And thank you for the explanation as well. That really helped.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Yorkshire Lad View Post
    How would I use this in the criterea field in my query design view?
    just type the "less than" operator and copy/paste the expression
    Attached Thumbnails Attached Thumbnails query-criteria.gif  
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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