Sql-server – Query With Date Ranges

datesql serversql-server-2008-r2t-sql

I have a stored procedure that I want to 1st gather data between a @startdate & @enddate then I want to compare that data to the previous year.

I tried the below syntax (for testing obviously) to ensure I was getting my desired dates output before I added to my production syntax. However, this gives me an error of

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '–'.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '–'.

This is the syntax I tried. What would I alter in order for my required dates to be output?

Declare @startdate date = '20170101'
    ,@enddate date = '20170212'

--one year prior to @startdate
Select CAST(DateAdd(yy, -1, @startdate) As Date)

--One year prior to @enddate
SELECT CAST(DateAdd(yy, -1, @enddate) As Date)

--This will get first day of prev yr
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0,
        DATEADD(YEAR, –1, @startdate)), 0)

--This will get last day of prev yr
SELECT DATEADD(MILLISECOND, –3, DATEADD(YEAR,
        DATEDIFF(YEAR, 0, DATEADD(YEAR, –1, @enddate)) + 1, 0))

EDIT
What I want to return is 

01/01/2017
02/12/2017
01/01/2016
12/31/2016

Best Answer

Not sure where you copied this code from, but it looks like it came from a web site that translated minus signs (-) to long dashes (–). You can see this in Management Studio if you turn IntelliSense on:

enter image description here

Here is the easy way to compare the same range as the year before:

DECLARE @startdate date = '20170101',
        @enddate   date = '20170212';

SELECT @startdate, DATEADD(YEAR, -1, @startdate),
       @enddate,   DATEADD(YEAR, -1, @enddate);

This compares the same period as last year, but I'm not sure that's what you're after since you are also using the zero date and datediff - not clear why. It does not account for leap year; you'll need to define what should happen if the range involves 2/29 either this year or last year.

When you use those dates in a query, don't do this subtracting 3 milliseconds nonsense - it only leads to problems. If you're trying to get all the data for February 12th even if it has time, then use < DATEADD(DAY, 1, @enddate). Also, don't use YY, just type out YEAR. It is also far easier to get the full range of last year:

-- for SQL Server 2012+
WHERE col >= DATEFROMPARTS(YEAR(@startdate)-1, 1, 1)
  AND col <  DATEFROMPARTS(YEAR(@startdate), 1, 1);

-- for older, unsupported versions:
WHERE col >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
  AND col <  DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');

To sum up:

DECLARE @startdate date = '20170101',
        @enddate   date = '20170212';

-- sales from that period this year:

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= @startdate
    AND some_datetime_column <  DATEADD(DAY, 1, @enddate);

-- sales from same period previous year (again, not accounting for leap special case):

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= DATEADD(YEAR, -1, @startdate)
    AND some_datetime_column <  DATEADD(YEAR, -1, DATEADD(DAY, 1, @enddate));

-- sales from all of previous year:

SELECT SUM(some_column) FROM dbo.SomeTable
  WHERE some_datetime_column >= DATEADD(YEAR, YEAR(@startdate)-1901, '19000101'),
    AND some_datetime_column <  DATEADD(YEAR, YEAR(@startdate)-1900, '19000101');

Suggested reading:

Related Question