Filter Date Range

Other SQL Server Topics
#1

Hi Experts,
I'm attempting to obtain information from a customer transaction table's invoice_date column.

We need data on the set of clients' YTD (year to date) and PYTD (previous year to date) sales.

Example: From 01-01-2022 to 23-05-2022 and 01-01-2023 to 23-05-2023.
(The date will keep on changing)

Please, could you assist with the request?

Thank You
Yoga

#2 
SELECT 
    -- Beginning of this year
    CAST(DATEADD(YEAR,DATEDIFF(YEAR,'19000101',GETDATE()),'19000101') AS DATE),
    -- Same date last year
    CAST(DATEADD(YEAR, -1, GETDATE() ) AS DATE),
    -- Beginning of last year
    CAST(DATEADD(DAY, -1, DATEADD(YEAR,DATEDIFF(YEAR,'19000101',GETDATE()),'19000101')) AS DATE)

Observe the behavior when current year or prior year is a leap year and date is Feb 28 or 29 and make sure that that is what you want.

#3

For YTD or PYTD you need to calculate the first of this year, the first of last year and the current date this year and same date last year:

DECLARE @current_year = DATEADD(year, DATEDIFF(year, 0, getdate()), 0)
      , @current_date = CAST(getdate() AS date);

DECLARE @previous_year = DATEADD(year, -1, @curYearStart)
      , @previous_date = DATEADD(year, -1, @current_date);

Now, you just use those in the query:

SELECT ...
  FROM ...
 WHERE (date_column >= @current_year AND date_column < @current_date)
    OR (date_column >= @previous_year AND date_column < @previous_date)