0

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))
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lowell Lowellcraft
  • 1
  • 2
  • What error or issue you are facing? – Shreyas Prakash Oct 10 '21 at 09:30
  • The above query is fetching records from 1st September to 28th September but it is missing august records when I run this query on 1st october 2021. – Lowell Lowellcraft Oct 10 '21 at 09:58
  • We need to know the **exact** data-types of your columns. Please post your `CREATE TABLE` statements. – Dai Oct 10 '21 at 10:30
  • Also as general guidance, in queries you should not wrap date/datetime columns in functions like `DATEADD` because that will wreck the performance of your query because it won't be SARGable anymore: https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable – Dai Oct 10 '21 at 10:31
  • 1
    What happens if it's Feburary (which has 28 days, not 29 - except on leap years)? – Dai Oct 10 '21 at 10:41
  • Are time-zones a concern at all? Does the SQL Server's system timezone correspond to the timezone used by the stored dates? – Dai Oct 10 '21 at 10:44
  • I am not concerned of timezones at all. Date data type is varchar. – Lowell Lowellcraft Oct 10 '21 at 22:11

2 Answers2

2

  • 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) ) )
Dai
  • 141,631
  • 28
  • 261
  • 374
  • Can't you go straight to `DATEFROMPARTS( YEAR(@today), MONTH(@today), 29)` then `@prevMonth29th = DATEADD( month, -1, @The29OfThisMonth )` also how do you deal with February – Charlieface Oct 10 '21 at 11:01
  • 2
    @Charlieface How the query _should_ handle February is a business-domain question which I asked the OP in a comment but they haven't responded yet. – Dai Oct 10 '21 at 11:03
  • @Charlieface Yes, I suppose you could use `DATEFROMPARTS` like that - but I don't recommend using it with a hardcoded `29` parameter because it will fail for February. My approach of using separate variables makes it easier to change the logic w.r.t. Feburary. – Dai Oct 10 '21 at 11:05
0

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.

Jeff
  • 512
  • 2
  • 8