Comparing year over year data

I am wanting to pull data for a date range in 2017 then pull the same date range but for 2016. I thought I would get crafty and use DateAdd() feature, but I am missing something here as I get an error of


Msg 241, Level 16, State 1, Line 14

Conversion failed when converting date and/or time from character string.

What I want to do is get a COUNT(saledate) that fall between the time period which will give me the number of sales. This is sample DDL

Declare @startdate varchar(100), @enddate varchar(100), @prevyrstartdate varchar(100), @prevyrenddate varchar(100)

SET @startdate = '01/01/2017'
SET @enddate = '01/31/2017'
SET @prevyrstartdate = CAST(DateAdd(yy, -1, @startdate) As Date)
Set @prevyrenddate = CAST(DateAdd(yy, -1, @enddate) As Date)

Declare @HoldingPattern Table
(
    salesman varchar(100)
    ,saledate date
)

Insert Into @HoldingPattern (salesman, saledate) VALUES
('BOB', '2017-01-23')
,('BOB', '2017-01-30')
,('BOB', '2017-01-24')
,('BOB', '2017-01-30')
,('BOB', '2017-01-30')
,('BOB', '2017-01-30')
,('BOB', '2017-02-30')
,('BOB', '2017-02-27')
,('BOB', '2017-03-31')
,('BOB', '2017-03-27')
,('BOB', '2016-01-23')
,('BOB', '2016-01-30')
,('BOB', '2016-01-24')
,('BOB', '2016-01-30')
,('BOB', '2016-03-30')
,('BOB', '2016-03-30')
,('BOB', '2016-03-30')
,('BOB', '2016-02-27')
,('BOB', '2016-02-31')
,('BOB', '2016-02-27')

Select
salesman
,[2017 Data] = (Select COUNT(CAST(saledate As nvarchar(100))) FROM @HoldingPattern where saledate between @startdate AND @enddate)
,[2016 Data] = (Select COUNT(CAST(saledate As nvarchar(100))) FROM @HoldingPattern where saledate between @prevyrstartdate and @prevyrstartdate)
FROM @HoldingPattern

Bad data, bad data types, and bad formatting aside, if you're getting a month of data for present and previous year, you don't need to have four variables; one will do. But it should be a date, not a string. I also recommend avoiding BETWEEN since it only works in an extremely narrow set of circumstances.

DECLARE @startdate date = '20170101';

DECLARE @HoldingPattern TABLE
(
  salesman varchar(100),
  saledate date
);

-- let's ignore the illogical dates
INSERT @HoldingPattern (salesman, saledate) VALUES
 ('BOB', '20170123'),('BOB', '20170130'),('BOB', '20170124'),
 ('BOB', '20170130'),('BOB', '20170130'),('BOB', '20170130'),
 /*('BOB', '20170230'),*/('BOB', '20170227'),('BOB', '20170331'),
 ('BOB', '20170327'),('BOB', '20160123'),('BOB', '20160130'),
 ('BOB', '20160124'),('BOB', '20160130'),('BOB', '20160330'),
 ('BOB', '20160330'),('BOB', '20160330'),('BOB', '20160227'),
 /*('BOB', '20160231'),*/('BOB', '20160227');

SELECT
  Salesman,
  [2017 Data] = COUNT(CASE WHEN saledate >= @startdate 
    AND saledate = DATEADD(YEAR, -1, @startdate) 
    AND saledate < DATEADD(YEAR, -1, DATEADD(MONTH, 1, @startdate)) THEN 1 END)
FROM @HoldingPattern
GROUP BY Salesman;

Result:

Salesman    2017 Data    2016 Data
--------    ---------    ---------
BOB         6            4

Further reading:

• #BackToBasics : Dating Responsibly

• Bad habits to kick : mis-handling date / range queries

• Bad Habits to Kick : Using shorthand with date/time operations

• What do BETWEEN and the devil have in common?

StackExchange Database Administrators Q#162923, answer score: 9