Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    15

    Unanswered: Select last months data

    Hi, i am trying to create a t-sql statement that will retrieve last months data (ie. if i run the query on 9th August, i only want to retrieve Julys data, 1st Sept will retrieve all of Augusts data etc). The query will be used once a month to populate a table, can anyone advise me on the correct where clause to use ?

    Thanks in advance

    Dave

  2. #2
    Join Date
    May 2004
    Posts
    15
    ok, i forgot to mention the column i need to run my where clause against...its called 'OrderDate' and the type is datetime.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use something like:
    Code:
    DECLARE @dLastMonthBegin DATETIME
    ,  @dLastMonthEnd DATETIME
    
    SELECT @dLastMonthBegin = Convert(CHAR(8), DateAdd(month, -1, GetDate(), 121))
    +  '01', @dLastMonthEnd = DateAdd(ms, -3, Convert(CHAR(8)
    ,  GetDate(), 121) + '01'))
    -PatP

  4. #4
    Join Date
    May 2004
    Posts
    15
    i have tested a few ways of achieving this, the sql has to be embedded in a scheduled dts package so i have used the following:

    select * from mytable where
    where datediff(m, [DATECOLUMN], getdate())=1

    Thanks for your comment.

    Dave

Posting Permissions

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