returning transactions from the last 12 whole months

  • john.dixon 58151

    SSC Enthusiast

    Points: 129

    Hi,

    I'm trying to write a query to return all transactions with a transaction date from within the last 12 whole months prior to today's date.

    So for example, if today's date is 10th March 2017, the query should return all transactions between 01 March 2016 and 28 Feb 2017.

    I've got this far:

    WHERE CONVERT(date, t1.transactiondate) BETWEEN DATEADD(month,-13,GETDATE()) AND DATEADD(month,-1,GETDATE())

    ...but this gives me all transactions between 11th Feb 2016 and 10th Feb 2017 - close, but not correct.

    I'm new to SQL so all help welcome - please be gentle 🙂

  • John Mitchell-245523

    SSC Guru

    Points: 148218

    WHERE CONVERT(date, t1.transactiondate) >=  DATEADD(month,DATEDIFF(month,'19800101',GETDATE())-12,'19800101')
    AND CONVERT(date, t1.transactiondate) < DATEADD(month,DATEDIFF(month,'19800101',GETDATE()),'19800101')

    John

  • Sean Lange

    SSC Guru

    Points: 286363

    john.dixon 58151 - Friday, March 10, 2017 8:44 AM

    Hi,

    I'm trying to write a query to return all transactions with a transaction date from within the last 12 whole months prior to today's date.

    So for example, if today's date is 10th March 2017, the query should return all transactions between 01 March 2016 and 28 Feb 2017.

    I've got this far:

    WHERE CONVERT(date, t1.transactiondate) BETWEEN DATEADD(month,-13,GETDATE()) AND DATEADD(month,-1,GETDATE())

    ...but this gives me all transactions between 11th Feb 2016 and 10th Feb 2017 - close, but not correct.

    I'm new to SQL so all help welcome - please be gentle 🙂

    You can accomplish by doing some date math. In my example I am using 0 as a date. In sql server this will implicitly become 1900-01-01. Here is an example of getting the beginning of the current month last year and the last day of the current month.


    select BeginningOfThisMonthLastYear = dateadd(year, -1, dateadd(month, datediff(month, 0, getdate()), 0))
        , EndOfThisMonth = dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0))

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Bill Talada

    SSChampion

    Points: 11955

    Maybe something like this.  Generally, I use a function to normalize all date ranges for all reports.


    SELECT
        CONVERT(varchar(6),DATEADD(MONTH,-12,GETDATE()),112)+'01',
        CONVERT(varchar(8),DATEADD(day,-DATEPART(DAY,GETDATE()),GETDATE()),112)
    ;

  • John Mitchell-245523

    SSC Guru

    Points: 148218

    Bill Talada - Friday, March 10, 2017 9:14 AM

    Maybe something like this.  Generally, I use a function to normalize all date ranges for all reports.


    SELECT
        CONVERT(varchar(6),DATEADD(MONTH,-12,GETDATE()),112)+'01',
        CONVERT(varchar(8),DATEADD(day,-DATEPART(DAY,GETDATE()),GETDATE()),112)
    ;

    Careful with that.  Character conversions don't perform as well as date arithmetic.  I ran the two batches below several times against a table with nearly 4000 objects.  The first query had CPU and elapsed times around 14 seconds, the second between two and three seconds.
    set STATISTICS time on
    DECLARE @t table (d1 date, d2 date)
    insert into @t
    select CONVERT(varchar(6),DATEADD(MONTH,-12,o1.create_date),112)+'01' AS d1,
      CONVERT(varchar(8),DATEADD(day,-DATEPART(DAY,GETDATE()),o1.modify_date),112) AS d2
        from sys.objects o1 cross join sys.objects o
    go
    DECLARE @t table (d1 date, d2 date)
    insert into @t
    select    DATEADD(month,DATEDIFF(month,'19800101',GETDATE())-12,'19800101') AS d1,
    DATEADD(month,DATEDIFF(month,'19800101',GETDATE()),'19800101') AS d2
    from sys.objects o1 cross join sys.objects o
    GO

    John

  • john.dixon 58151

    SSC Enthusiast

    Points: 129

    Sean Lange - Friday, March 10, 2017 9:06 AM

    john.dixon 58151 - Friday, March 10, 2017 8:44 AM

    Hi,

    I'm trying to write a query to return all transactions with a transaction date from within the last 12 whole months prior to today's date.

    So for example, if today's date is 10th March 2017, the query should return all transactions between 01 March 2016 and 28 Feb 2017.

    I've got this far:

    WHERE CONVERT(date, t1.transactiondate) BETWEEN DATEADD(month,-13,GETDATE()) AND DATEADD(month,-1,GETDATE())

    ...but this gives me all transactions between 11th Feb 2016 and 10th Feb 2017 - close, but not correct.

    I'm new to SQL so all help welcome - please be gentle 🙂

    You can accomplish by doing some date math. In my example I am using 0 as a date. In sql server this will implicitly become 1900-01-01. Here is an example of getting the beginning of the current month last year and the last day of the current month.


    select BeginningOfThisMonthLastYear = dateadd(year, -1, dateadd(month, datediff(month, 0, getdate()), 0))
        , EndOfThisMonth = dateadd(day, -1, dateadd(month, datediff(month, 0, getdate()), 0))

    Wow, thanks all for quick and helpful responses.  I'm going with Sean's solution, I just need a quiet moment to unpick exactly how the datediff and dateadd arguments are combining!  Thanks again everyone 🙂

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply