I would like a query that would include from day 29 of last to last month to 28 of previous month.

Example: 29/08/2021 (29 August 2021) - 28/09/2021 (28 September 2021)

If today is 1/10/2021 (1st October 2021), I'll need to get the records of 29/08/2021 (29 August 2021) - 28/09/2021 (28 September 2021).

Please let me know how to do this with a SQL query?

WHERE Date BETWEEN DATEADD(month, -2, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 29)) 
               AND DATEADD(month, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 28))

CodePudding user response：

• I recommend avoiding SQL's BETWEEN operator:

  • It behaves differently depending on the actual type of the source data (e.g. date vs datetime, etc)..
  • It isn't obvious to non-expert readers if the range-comparison uses an inclusive or exclusive upper-bound.
  • Protip: range comparisons (especially date-range comparisons) are much easier to reason about if you consistently use inclusive-lower-bounds but exclusive upper-bounds - this is especially useful when doing date-range lookups on month and year boundaries.
    • ...otherwise you need to manually determine what the inclusive upper-bound is, which means getting the last-day-of-month - or handling leap-days and leap-seconds.
    • This approach also means you can use the exact same query for date and datetime values - whereas you'll be unintentionally incorrectly excluding data if you use an inclusive-upper-bound date value when the underlying data is datetime.
  • BETWEEN also makes it harder to use NULL-able operands with BETWEEN.
  • Instead, use explicit comparison operators: col >= inclusive-lower-bound and col < exclusive-upper-bound

• As for your problem: I recommend that you calculate the 29th day and store it in a DECLARE'd variable and use that, like so:

  • I assume your "Date" column is typed as date NOT NULL.

DECLARE @today date = CONVERT( date, GETDATE() );

DECLARE @startOfThisMonth date = DATEFROMPARTS( YEAR(@today), MONTH(@today), 1);
DECLARE @startOfPrevMonth date = DATEADD( month, -1, @startOfThisMonth );
DECLARE @prevMonth29th    date = DATEADD( day, 29, @startOfPrevMonth );
DECLARE @thisMonth28th    date = DATEADD( day, 28, @startOfThisMonth );

DECLARE @dateLowerIncl    date = @prevMonth29th;
DECLARE @dateUpperExcl    date = DATEADD( day, 1, @thisMonth28th ); /* Add  1 days so it's an exclusive upper-bound. */

SELECT
    *
FROM
    myTable AS t
WHERE
    t.[Date] >= @dateLowerIncl
    AND
    t.[Date] < @dateUpperExcl

While you can eliminate the DECLARE statements and compact everything into a single line, it becomes hideously unreadable and the loss of named variables means the query is no-longer self-describing, so don't do this.

SELECT
    *
FROM
    myTable AS t
WHERE
    t.[Date] >= DATEADD( day, 29, DATEADD( month, -1, DATEFROMPARTS( YEAR( GETDATE() ), GETDATE(), 1) ) )
    AND
    t.[Date] < DATEADD( day, 1, DATEADD( day, 28, DATEFROMPARTS( YEAR( GETDATE() ), MONTH( GETDATE() ), 1) ) )

CodePudding user response：

Unless I am missing something - this is not as hard as everyone is making it:

Declare @currentMonth datetime = datetimefromparts(year(getdate()), month(getdate()), 28, 0, 0, 0, 0);

Declare @startDate datetime = dateadd(month, -2, @currentMonth)
      , @endDate datetime = dateadd(month, -1, @currentMonth);

 Select @startDate, @endDate;

 --==== Your Query
 Select ...
   From {your Table}
  Where [Date] >= dateadd(day, 1, @startDate)
    And [Date] <  dateadd(day, 1, @endDate);

If your date column is a datetime - the above will work. It will include from the prior 28th date 1 (March 1st for non-leap years or Feb 29th for leap years), up to but not including the 29th of the previous month.

This logic works for the date data type also

Declare @currentMonth date = datefromparts(year(getdate()), month(getdate()), 28);

Declare @startDate date = dateadd(month, -2, @currentMonth)
      , @endDate date = dateadd(month, -1, @currentMonth);

 Select @startDate, @endDate;

 --==== Your Query
 Select ...
   From {your Table}
  Where [Date] >= dateadd(day, 1, @startDate)
    And [Date] <  dateadd(day, 1, @endDate);

For @startDate - we get '2021-08-28 00:00:00.000' and @endDate we get '2021-09-28 00:00:00.000'. The query then adds 1 day to each variable and the query uses >= '2021-08-29 00:00:00.000' and < '2021-09-29 00:00:00.000'.

If we set our current date to April 28 2021 - we get 2021-02-28 for the start date and we include everything from 2021-03-01. If we set current date to '2020-04-28' we get the same start date, but the range starts at 2020-02-29.