Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2012
    Posts
    11

    How to pull current month data from table updated daily

    Hey folks, novice programmer, first time forum user:

    I have a table thats updated daily with monthly data totals:
    Month, Total orders1, Total orders2, etc
    12/01/2012, 5, 8, etc
    11/01/2012, 6, 5, etc

    How do I pull data from this table in SQL Server for ONLY the current month? I was thinking using the getdate() function to get the current month, but it doesn't match exactly so I get no results

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,536
    Code:
    select *
    from table
    where [month] = convert(date, dateadd (dd, -1 * datepart(dd, getdate()) + 1, getdate()))
    By the way, "Month" is a reserved word in SQL Server.

  3. #3
    Join Date
    Dec 2012
    Posts
    11
    Thank you, the column name is actually ordermonth, I was just trying to shorten it a bit.
    I tried that code but got this error msg:

    Server: Msg 243, Level 16, State 1, Line 1
    Type date is not a defined system type.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    where OrderMonth >= DATEADD(month, DATEDIFF(month, '1900-01-01', getdate()), '1900-01-01') --Returns current month
    and OrderMonth < DATEADD(month, DATEDIFF(month, '1900-01-01', getdate())+1, '1900-01-01') --Returns next month
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Dec 2012
    Posts
    11
    That worked perfectly! Thank you!!
    One question tho..I figured out this little bit on my own and it seems to work:

    WHERE datepart(mm,ordermonth) =
    (SELECT datepart(mm,getdate()))

    Is there any reason why this would be any better or worse than Blindman's suggestion? Again, I'm brand new at this stuff, so I'm just trying to figure out what does what and how..
    Thank you again!
    Last edited by JayGrrr; 12-11-12 at 18:09.

  6. #6
    Join Date
    Dec 2012
    Posts
    11
    Oh, mine returns data for every year w current month in date, not just current month.. Crap. Got it.

  7. #7
    Join Date
    May 2008
    Posts
    97
    Why do people use system reserved words as a column name.. I learned in college to stay away after spending an entire weekend diagnosing why my access database connection wasn't working with java.

    I still feel the pain to this day.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    Because Microsoft reserved all the best words.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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