#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Select all from table in current month


    Hi

    How would I select * from a table where the data is always in the current month?

    I have some code that kind of works in MS SQL

    Code:
    select * from ainvdet WHERE [invoice_date] BETWEEN DATEADD(month,DATEDIFF(month,0,[invoice_date])-12,0) AND DATEADD(month,DATEDIFF(month,0,[invoice_date])-7,0)
    But it a) doesn't run in postgres and b) returns the last 6 months

    Any help would be great
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Code:
    select *
    from ainvdet 
    where date_trunc('month', invoice_date) = date_trunc('month', current_date);
    or
    Code:
    select *
    from ainvdet 
    where extract(year from invoice_date) = extract(year from current_date)
      and extract(month from invoice_date) = extract(month from current_date);
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo