locked
How to write a condition to get the last 30 days results ? RRS feed

  • Question

  • Hi frnz how to get detils of last 30 days,using getdate column in sql server 2008

    select ResourceID,
    ResourceType,
    Name0,
    Creation_Date0
    SMS_Unique_Identifier0,
    Resource_Domain_OR_Workgr0,3
    Client0 from v_R_System 
    where (DATEDIFF(MM,Creation_Date0,GETDATE())<=30)

    I have writen this query but not able to get the last 30 days result, i will get all the records

    • Edited by Ychinnari Thursday, January 15, 2015 2:36 PM
    Thursday, January 15, 2015 2:05 PM

Answers

  • Try this:

    Where  Creation_Date0 >= DATEADD(month,-1,GETDATE())


    Andy Tauber
    Data Architect
    The Vancouver Clinic
    Website | LinkedIn

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, January 15, 2015 4:03 PM
  • You've used MM. You need to give day in your Query

     declare @Creation_Date0 date='20141225'
     select 1 where DATEDIFF(day,@Creation_Date0,GETDATE())<=30
    --Prashanth

    Thursday, January 15, 2015 5:39 PM
  • better way is this to take advantage of an index if present in date field

    select ResourceID,
    ResourceType,
    Name0,
    Creation_Date0
    SMS_Unique_Identifier0,
    Resource_Domain_OR_Workgr0,3
    Client0 from v_R_System 
    where Creation_Date >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-30)
    AND Creation_Date < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

    assuming you want last 30 days data excluding current day

    see

    http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 15, 2015 5:41 PM

All replies