0

I am querying a sales order table and need to add a variable so that the query always returns a customer list for all customers that have not purchased this year. I don't want to hard code this years' date into WHERE or HAVING, but instead am interested to somehow have this dynamically pass the correct variable so that it also works in the future without changing the code. I appreciate any suggestions. Hope this is clear enough.

1

There are many ways to get the beginning of this year, I think the most intuitive is DATEFROMPARTS():

DECLARE @BeginningOfThisYear date = DATEFROMPARTS(YEAR(SYSUTCDATETIME()),1,1);

SELECT ... FROM dbo.Customers AS c
WHERE NOT EXISTS 
(
  SELECT 1 FROM dbo.Orders 
    WHERE CustomerID = c.CustomerID
    AND OrderDate >= @BeginningOfThisYear
);

Less intuitive ways include:

DECLARE @BeginningOfThisYear date = DATEADD(DAY, 
  1-DATEPART(DAYOFYEAR, SYSUTCDATETIME()), 
  SYSUTCDATETIME()
);

And downright terrible ways include:

DECLARE @BeginningOfThisYear date = CONVERT(date,
  CONCAT(YEAR(SYSUTCDATETIME()),'0101'));
0

There could be many different ways to do this, this is one:

Declare @SO Table (SOID int,
date datetime)
insert into @so
    (SOID, DATE)
VALUES
    (1, dateadd(yy,-1,getdate())),
    (2, dateadd(mm,-1,getdate()));
SELECT SOID, Date
from @SO
WHERE Year(date) = year(getdate())

Same idea with a variable:

DECLARE @currycar smalldatetime = getdate()
DECLARE  @SO Table (SOID int,
    date datetime)
insert into @so
    (SOID, DATE)
VALUES
    (1, dateadd(yy,-1,getdate())),
    (2, dateadd(mm,-1,getdate()));
SELECT SOID, Date
from @SO
WHERE Year(date) = year(@currycar)
  • 3
    I wouldn't use YEAR(col) because if there is an index on date (now or in the future) this will ensure it can't be used. – Aaron Bertrand May 30 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.