CODEWITHSUNDEEP
sqlsql-servert-sql
bmsqldev
bmsqldev

Reputation: 2735

Get records between two datetimes in SQL Server

I have found below query in one of our existing Stored Procedures. This query used to take records between two datetime values.

SELECT office
FROM   officebudget
WHERE  officeid = @officeid
       AND (
               (CONVERT(DATE, DateFrom) BETWEEN @wkstdate AND @wkenddate)
               OR (CONVERT(DATE, DateTo) BETWEEN @wkstdate AND @wkenddate));

I have re-written it as below,

SELECT office
FROM   officebudget
WHERE  officeid = @officeid
       AND (
               (
                   bkto.DateFrom >= @wkstdate
                   AND bkto.DateFrom <= @wkenddate
               )
               OR (bkto.DateTo >= @wkstdate
               AND bkto.DateTo <= @wkenddate)
           );

I got the same result for both cases. but i need to know in any of the scenarios where both of the above queries will produce different results?

(P.S: DateFrom, DateTo, @Wkstdate, @Wkenddate are datetime fields)

Upvotes: 0

Views: 235

Answers (3)

japzdivino
japzdivino

Reputation: 1746

In some cases, it will give you different result, or worst NO result at all.

The reason why the old code has a CONVERT is , the developer want to assure that he is comparing the same exact datatype to be able to avoid errors or unexpected result. Please see my example below to further understand.

This one will return FALSE : something like give you different result.

    DECLARE @date1 DATETIME = GETDATE() --2015-10-05 16:15:54.780 , i used date today
    DECLARE @date2 DATETIME = '20151005'

    IF @date1 = @date2
    BEGIN
        SELECT 'TRUE'
    END
    ELSE
        SELECT 'FALSE'

And this one will return TRUE: something like give you same result

    DECLARE @date1 DATETIME = CONVERT(DATE,GETDATE())
    DECLARE @date2 DATETIME = CONVERT(DATE,'20151005')

    IF @date1 = @date2
            BEGIN
                SELECT 'TRUE'
            END
            ELSE
                SELECT 'FALSE'

Hope my sample helps you in analyzing the your codes, my advise is CONVERT it first before comparing the two dates for you to get the exact result.

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

I suspect that you are refactoring your query to make it sargable and to use possible indexes on columns DateFrom and DateTo.

Those will not result in same results because your query will omit rows where date part of wkenddate equals datepart of DateFrom or DateTo column values. For example let's say wkenddate = '20151005' and your column DateFrom = '20151005 15:30'. First query will include this row since both dateparts are equal. And your second query will omit this row since '20151005 15:30' > '20151005'.

Consider these example:

DECLARE @t TABLE(d DATETIME)

INSERT INTO @t VALUES
('20151001 10:30'),
('20151004 10:30'),
('20151005 10:30')

DECLARE @wkstdate DATE = '20151001', @wkenddate DATE = '20151005'

SELECT * FROM @t WHERE CAST(d AS DATE) BETWEEN @wkstdate AND @wkenddate
SELECT * FROM @t WHERE d >= @wkstdate AND d <= @wkenddate
SELECT * FROM @t WHERE d >= @wkstdate AND d < DATEADD(dd, 1, @wkenddate)

Outputs:

2015-10-01 10:30:00.000
2015-10-04 10:30:00.000
2015-10-05 10:30:00.000

2015-10-01 10:30:00.000
2015-10-04 10:30:00.000

2015-10-01 10:30:00.000
2015-10-04 10:30:00.000
2015-10-05 10:30:00.000

You should rewrite as:

SELECT office
FROM   officebudget
WHERE  officeid = @officeid
       AND (
               (
                   bkto.DateFrom >= @wkstdate
                   AND bkto.DateFrom < dateadd(dd, 1 , @wkenddate)
               )
               OR (bkto.DateTo >= @wkstdate
               AND bkto.DateTo < dateadd(dd, 1, @wkenddate))
           );

Upvotes: 1

Gaston G
Gaston G

Reputation: 396

The difference is that in the first query, you use a function (CONVERT) on the filtered columns (FateFrom and DateTo), then the engine could not use the index on it if it exists and so the first query would tend to be slower.

In the second query, you have entered manually the date like this : 2015-09-30. It is better to enter it like this : 20150930 because this last form is not dependant of the region of the server, whereas the first form is.

Upvotes: 0

Related Questions