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
Best Answer
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.Result:
Further reading: