Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
list the last 4 months using GETDATE()

Отвечено list the last 4 months using GETDATE()

  • Monday, February 04, 2013 10:52 PM
     
     
    Sign In to Vote
    0

    Hi,

    I have some years of data, and I'm interested in looking for the last 4 months of data. for this I have written a WHERE condition like: 

     (DATEPART(month, mydatecolumn) BETWEEN DATEPART(month, GETDATE()) - 4 AND DATEPART(month, GETDATE()) - 1)

    This is not returning any records and the current month is February, its not returning anything.

    Can anyone let me know the correct syntax 

     

All Replies

  • Monday, February 04, 2013 11:10 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Run 

    SELECT DATEPART(month, GETDATE()) - 4

    and see if it makes any sense.

    You need to include the year as well.

    If you want to look at the data for the last four full months, you would need:

    mydatecolumn BETWEEN 
dateadd(MONTH, -4, convert(char(6), getdate(), 112) + '01') AND
dateadd(DAY, -1, convert(char(6), getdate(), 112) + '01')
    The key here is convert(char(6), getdate(), 112) + 01. this gives you YYYYMM01 for the current month. Then you can subtract 4 months to get four month back, and one day to get the last day in the month before.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked As Answer by K.Kalyan Monday, February 04, 2013 11:47 PM
    •  
     
  • Monday, February 04, 2013 11:20 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Erland's answer is correct if your column mydatecolumn is of type date.  If it is datetime or datetime2, then you should use

    mydatecolumn >=
dateadd(MONTH, -4, convert(char(6), getdate(), 112) + '01') AND
mydatecolumn < convert(char(6), getdate(), 112) + '01')

    Tom

     
  • Tuesday, February 05, 2013 12:09 AM
     
     
    Sign In to Vote
    0
    Thanks for your help.