none
T-SQL Dateadd function RRS feed

  • Question

  • How can I automatically declare the last 6 COMPLETE months?

    For example, today is 1/20/10, so I would like the StartDate to be 7/1/09 and the end date to be 12/31/09.

    This is what I currently use:
    set @@StartDate = '2009-07-01'
    set @@EndDate = '2009-12-31'

    How can I write this in t-sql using the dateadd function so that it is automatic and I don't need to manually change the code every time?

    Thankyou!
    Rodney
    Wednesday, January 20, 2010 2:59 PM

Answers

All replies

  • Is this what you need?

    DECLARE @t DATETIME = '5/20/10'
    SELECT DATEADD(dd,-DAY(DATEADD(mm,-6,@t))+1,DATEADD(mm,-6,@t)) AS StartDate
    	,DATEADD(dd,-DAY(DATEADD(mm,0,@t)),DATEADD(mm,0,@t)) AS EndDate


    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, January 20, 2010 3:07 PM
  • Hi...

    Try Like This
    Select 
    DateAdd(dd,-DatePart(DD,GetDate())+1,DateAdd(mm,-6,GETDATE())) 'FirstDate', 
    DateAdd(dd, -DatePart(dd,GetDate()), DATEADD(mm,1,GetDate())) 'SecondDate'
    

    Select * From TableName
    Where DateColumn Between DateAdd(dd,-DatePart(DD,DateColumn)+1,DateAdd(mm,-6,DateColumn)), 
    		DateAdd(dd, -DatePart(dd,DateColumn), DATEADD(mm,1,DateColumn))
    Thanks
    Best Regards,
    Gopi V

    If you have found this post helpful, please click the Vote as Helpful link (the green triangle and number on the top-left).

    If this post answers your question, click the Mark As Answered link below. It helps others who experience the same issue in future to find the solution.

    Wednesday, January 20, 2010 3:08 PM
  • Hi Rodney,

    Try this

    SELECT

     

    Convert(Char(10) , Dateadd(m , -6 , DATEADD(dd,-(DAY(GetDate())-1),GetDate())) ,103) AS Date_Value ,

     

    Convert(Char(10) , DATEADD(dd,-(DAY(GetDate())),GetDate()) , 103)


    Regards,

    Balwant.

    Wednesday, January 20, 2010 3:10 PM
  • DECLARE @date DATETIME 
    set @date = '7/20/10'
    Select 
    DateAdd(dd,-DatePart(DD,GetDate())+1,DateAdd(mm,-6,@date)) 'StartDate', 
    DateAdd(dd, -DatePart(dd,GetDate()), DATEADD(mm,0,@date)) 'EndDate'
    Wednesday, January 20, 2010 3:16 PM
  • Sorry, I didn't specify on my original post.  But I need from 12:00 AM on 7/1/09 to 11:59 PM on 12/31/09.

    When I run this:
    Select
    DateAdd(dd,-DatePart(DD,GetDate())+1,DateAdd(mm,-6,GETDATE())) 'FirstDate',
    DateAdd(dd, -DatePart(dd,GetDate()), DATEADD(mm,1,GetDate())) 'SecondDate'

    I get this:
    FirstDate = 2009-07-01 07:21:21.160
    SecondDate = 2010-01-31 07:21:21.160

    But I need this (notice the time):
    FirstDate = 2009-07-01 00:00:00.000
    SecondDate = 2010-12-31 11:59:59.999

    Thank you for the quick replies.
    Wednesday, January 20, 2010 3:24 PM
  • Try this

    DECLARE @t DATETIME = '5/20/10'
    SELECT DATEADD(dd,-DAY(@t)+1,DATEADD(mm,-6,@t)) AS StartDate
    	,DATEADD(ss,-1,DATEADD(dd,-DAY(@t)+1,DATEADD(mm,0,@t))) AS EndDate


    Abdallah El-Chal, PMP, ITIL, MCTS
    • Proposed as answer by srinivas56 Wednesday, January 20, 2010 3:31 PM
    • Edited by Abdshall Wednesday, January 20, 2010 3:35 PM Remove extra code
    • Marked as answer by Zongqing Li Tuesday, January 26, 2010 7:36 AM
    Wednesday, January 20, 2010 3:28 PM
  • 11:59PM is not going to work for all cases. It is much better to use 12:00 AM of the next day and use < instead of between operator.

    See this blog http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx for details.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, January 20, 2010 3:48 PM
    Moderator
  • That was an excellent article. Thanks for the link!
    Wednesday, January 20, 2010 4:27 PM
  • See all other articles in this series http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/series-of-bad-habits-to-kick-by-aaron-be
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, January 20, 2010 4:33 PM
    Moderator
  • Following is an accurate solution for datetime range from Abdallah's post.

    DECLARE @t DATETIME = '2010-01-23'
    SELECT DATEADD(dd,-DAY(@t)+1,DATEADD(mm,-6,@t)) AS StartDate
    	,DATEADD(dd,-DAY(@t)+1,DATEADD(mm,0,@t)) AS EndDate
    
    /* StartDate	EndDate
    2009-07-01 00:00:00.000	2010-01-01 00:00:00.000
    */

    Use >= operator with the StartDate and the < operator with the EndDate.

    DATETIME range programming:

    http://www.sqlusa.com/bestpractices2008/between-dates/

    Date & time functions examples:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012




    Monday, January 25, 2010 9:50 AM
    Moderator
  •  Kalman and abdallah both are using an extra DateAdd function while it is giving same result without that extra function calll



    SELECT DATEADD(dd,-DAY(@t)+1,DATEADD(mm,-6,@t)) AS StartDate
          ,DATEADD(millisecond,-3,DATEADD(dd,-DAY(@t)+1,  @t )) AS EndDate
    
    /*
    StartDate                         EndDate
    2009-07-01 00:00:00.000 2009-12-31 23:59:59.997
    
    */
    

    Monday, January 25, 2010 10:35 AM
  • as what Peter highlighted,

    if the the StartDate and EndDate is used for filtering on a datetime column with time it will exclude the 23:59:59.xxx records.

    The EndDate should be the calcualted as 20100101 and when compare use LESS THAN < EndDate

     

    DECLARE @t         datetime = '20100120',
        @StartDate    datetime,
        @EndDate    datetime
    
    select  @StartDate = dateadd(month, datediff(month, 0, @t) - 6, 0),
        @EndDate = dateadd(month, datediff(month, 0, @t), 0)
    
    and use this in the comparison with the datecol as such
    where datecol >= @StartDate and datecol < @EndDate
    And if the datecol does not contain the time component, the EndDate can be calculated simply as

     

    select  @StartDate = dateadd(month, datediff(month, 0, @t) - 6, 0),
        @EndDate = dateadd(month, datediff(month, 0, @t), -1)
    and the comparison can be <=
    where datecol >= @StartDate
    and datecol <= @EndDate

     

     


    KH Tan
    Saturday, March 27, 2010 4:39 AM
  • 11:59PM is not going to work for all cases. It is much better to use 12:00 AM of the next day and use < instead of between operator.

    See this blog http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx for details.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    It is exactly what I need, It is good for reference.
    Sunday, October 3, 2010 1:30 AM