none
GETDATE() get previous month data

    Question

  • I have a date field within my table

    how do i write a WHERE clause whereby it looks at the current date getdate() and take the last month dataset.

    so it would be something like this

    SELECT *
    FROM MyTable
    WHERE time_pres BETWEEN getdate()..... and getdate()....

    My dataset looks like this...

    time_pres
    2011-06-11 19:30:30.000
    2011-06-11 19:30:33.000
    2011-06-11 19:32:30.000
    2011-06-11 19:39:00.000
    2011-06-11 19:44:00.000
    2011-06-11 19:52:00.000
    2011-06-11 20:00:00.000
    2011-06-11 20:20:00.000

    Monday, June 13, 2011 12:43 PM

Answers

  • Find the first day of the current month and the first day of the previous month (it is the first day of the curret month - 1); perhaps something like this:

     declare @test table( time_Pres datetime )
    insert into @test
    select '2011-04-30 23:59:59.997' union all
    select '2011-05-01' union all
    select '2011-05-31 23:59:59.997' union all
    select '2011-06-01'

    select *
    from @test
    where time_Pres >= dateadd(month, datediff(month, 0, getdate())-1, 0)
      and time_Pres <  dateadd(month, datediff(month, 0, getdate()), 0)

    /* -------- Output: --------
    time_Pres
    -----------------------
    2011-05-01 00:00:00.000
    2011-05-31 23:59:59.997

    (2 row(s) affected)
    */

    .


    • Marked as answer by Sam233 Monday, June 13, 2011 12:56 PM
    Monday, June 13, 2011 12:46 PM

All replies

  • Find the first day of the current month and the first day of the previous month (it is the first day of the curret month - 1); perhaps something like this:

     declare @test table( time_Pres datetime )
    insert into @test
    select '2011-04-30 23:59:59.997' union all
    select '2011-05-01' union all
    select '2011-05-31 23:59:59.997' union all
    select '2011-06-01'

    select *
    from @test
    where time_Pres >= dateadd(month, datediff(month, 0, getdate())-1, 0)
      and time_Pres <  dateadd(month, datediff(month, 0, getdate()), 0)

    /* -------- Output: --------
    time_Pres
    -----------------------
    2011-05-01 00:00:00.000
    2011-05-31 23:59:59.997

    (2 row(s) affected)
    */

    .


    • Marked as answer by Sam233 Monday, June 13, 2011 12:56 PM
    Monday, June 13, 2011 12:46 PM
  • SELECT * 
    FROM MyTable
    WHERE time_pres BETWEEN dateadd(m,datediff(m,0,getdate())-1,0) and dateadd(m,datediff(m,0,getdate()),0)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, June 13, 2011 12:46 PM
  • Declare @CurrentDate DateTime
    Declare @PrevMonthDate DateTime
    
    
    Set @CurrentDate=Dateadd(dd,datediff(d,0,getdate()),0)
    
    Set @PrevMonthDate=dateadd(month,-1,@CurrentDate)
    
    SELECT * 
    FROM MyTable
    WHERE time_pres>=@PrevMonthDate
    and time_pres<=@CurrentDate
    


    Yogesh Bhadauriya
    My Blog
    Monday, June 13, 2011 12:50 PM
  • SELECT * 
    FROM MyTable
    WHERE time_pres BETWEEN dateadd(m,datediff(m,0,getdate())-1,0) and dateadd(m,datediff(m,0,getdate()),0)

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/


    Is there a problem with the BETWEEN operator:

    SELECT * 
    FROM @test
    WHERE time_pres BETWEEN dateadd(m,datediff(m,0,getdate())-1,0) and dateadd(m,datediff(m,0,getdate()),0)
    
    /* -------- Output: --------
    time_Pres
    -----------------------
    2011-05-01 00:00:00.000
    2011-05-31 23:59:59.997
    2011-06-01 00:00:00.000
    
    (3 row(s) affected)
    */
    

    ??

    Shouldn't that last entry be excluded?

    Monday, June 13, 2011 12:53 PM
  • Select * from MyTable
    Where time_pres Between Dateadd(mm,-1,getdate()) ANd getdate()
    


    Please visit my Blog for some easy and often used t-sql scripts
    Monday, June 13, 2011 12:54 PM
  • Yes , because BETWEEN translates into 

    where time_Pres >= dateadd(month, datediff(month, 0, getdate())-1, 0)
      and time_Pres <=  dateadd(month, datediff(month, 0, getdate()), 0)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, June 13, 2011 1:08 PM