paul.j.kemna (1/7/2013)

---

The actual syntax would be :

DECLARE @StartDate as date
SET @StartDate = some calculated date

DECLARE @EndDate as date
SET @EndDate = another calculated date


CONVERT([varchar](10), MyDatetimeColumn, 101) AS MyDate
...
WHERE MyDate >= @StartDate and MyDate <= @EndDate


I am not so concerned about efficiency as I am accuracy at this time.

WHERE MyDate >= @StartDate and MyDate < @EndDate

paul.j.kemna (1/7/2013)

---

The actual syntax would be :

DECLARE @StartDate as date
SET @StartDate = some calculated date

DECLARE @EndDate as date
SET @EndDate = another calculated date


CONVERT([varchar](10), MyDatetimeColumn, 101) AS MyDate
...
WHERE MyDate >= @StartDate and MyDate <= @EndDate


I am not so concerned about efficiency as I am accuracy at this time.

GSquared (1/7/2013)

---

Assuming "correct results" means dates between the two variable values, including the end-date value, then yes.

As mentioned, using <= @EndDate can give odd results.

For example:

DECLARE @T TABLE
    (ID INT IDENTITY
            PRIMARY KEY,
     DT DATETIME);

INSERT  INTO @T
        (DT)
VALUES  ('2013-01-01 06:00'),
        ('2013-01-02 08:00'),
        ('2013-01-03 23:00');

DECLARE @StartDate DATE = '2013-01-02',
    @EndDate DATE = '2013-01-03';

SELECT  *
FROM    @T
WHERE   CONVERT(CHAR(10), DT, 101) >= @StartDate
        AND CONVERT(CHAR(10), DT, 101) <= @EndDate;

If "End Date" means "everything up to but not including", then this will give incorrect results. If it means everything up to and including, then it will give correct results.

So whether that will be correct or not depends on your definition of End Date.

If, for example, you want all of January 2013 in a query, then you have to assign Start Date as 1 Jan 13, and End Date as 31 Jan 13. It's a lot easier to calculate the first day of the next month, and use < instead of <=, because that skips the complexity of dealing with variable end dates (31 for Jan, 28 for Feb except when it's 29, 31 for March, 30 for April, etc.).

That's why most prefer to use >= Start Date, and < End Date, and define End Date as the "up to but not including". Then for January, you figure out the 1st of the month (dead easy), and you use DateAdd to add one month to it (again, dead easy), and you're done. No worrying about short/long/messed-up months that we inherited from Romans who couldn't figure out how to make December always be in the winter or just plain didn't care about that.

So I can't tell if it's correct or not, because I don't know how End Date is defined in your system.

Beyond that, yeah, it'll work correctly if End Date is defined as "up to and including".