Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2009
    Posts
    5

    Unanswered: SQL Help needed for DateAdd function please !

    G'day, I am trying to retrieve data for the current month and last 12 months and i am using the following script in the WHERE Statement: >= DATEADD(month, - 12, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) + 0, 0)))
    However, I cannot show the data for June 2009, is this because this month is not complete as of yet ? Im new to this so sorry if im not making much sense !

    Kind Regards
    Jake

  2. #2
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post

    Hi Jake
    If I understand well you need to get data for the last 12 months ?
    In this case you need to use BETWEEN and DATE_ADD().
    Your WHERE statement should be like this:
    Code:
    SELECT ...
    FROM ...
    WHERE ...
      AND MONTH(data_to_get_date)
        BETWEEN
          MONTH(DATE_ADD(NOW(), INTERVAL -12 MONTH))
        AND
          MONTH(NOW())

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Jake_Hall
    ... DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) + 0, 0)))
    this looks like SQL Server, not MySQL

    pls confirm which database you're using -- you posted in the MySQL forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2009
    Posts
    5
    Hi there, many thanks for both replies. I did infact post this in the wrong area, as I am using SQL Server but many thanks for your replies. R937 - i am using SQL Server Microsoft Visual Studio - do you have any ideas how I can return the 1st day of the month, I think the GETDATE function is "screwing this up for me" - thanks again guys

  5. #5
    Join Date
    Jun 2009
    Posts
    5

    DATEADD Function

    Hi there, I am trying to retrieve data for the previous 12 months, ordered by Month and year and this is working fine, however, due to using the GetDate statement, this is running from todays date 1 year ago until the present date. Can anyone advise me how I can gett his query to run from the 1st of each month please. The query I have written in the WHERE clause is :

    >= DATEADD(mm, - 12, DATEADD(dd, DATEDIFF(d, 0, GETDATE()) + 0, 0)))

    Kind Regards,
    Jake

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Jake_Hall
    Can anyone advise me how I can gett his query to run from the 1st of each month please.
    huh? from the first of each month?

    please describe what you mean by "the previous 12 months"

    do you want all of june 2008 through end of may 2009, or what?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I moved this thread to the Microsoft SQL Forum for you.

    This code is totally bogus, but it will do what you want:
    Code:
    SELECT DateAdd(month, DateDiff(month, 0, GetDate()), 0)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    I moved this thread to the Microsoft SQL Forum for you.
    maybe you could then merge it with this thread which jake started when he realized he was in the wrong forum

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

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT GetDate() As the_date
         , DateAdd(mm, DateDiff(mm, 0, GetDate()), 0) As first_of_the_month
         , DateAdd(mm, DateDiff(mm, 0, GetDate()) - 12, 0) As first_of_the_month_minus_12
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Merging now!
    Jake, please refrain from cross-posting the same question
    George
    Home | Blog

Posting Permissions

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