What Is the Optimal Way to Compare Dates in Microsoft SQL Server

What is the optimal way to compare dates in Microsoft SQL server?

Converting to a DATE or using an open-ended date range in any case will yield the best performance. FYI, convert to date using an index are the best performers. More testing a different techniques in article: What is the most efficient way to trim time from datetime? Posted by Aaron Bertrand

From that article:

DECLARE @dateVar datetime = '19700204';

-- Quickest when there is an index on t.[DateColumn], 
-- because CONVERT can still use the index.
SELECT t.[DateColumn]
FROM MyTable t
WHERE = CONVERT(DATE, t.[DateColumn]) = CONVERT(DATE, @dateVar);

-- Quicker when there is no index on t.[DateColumn]
DECLARE @dateEnd datetime = DATEADD(DAY, 1, @dateVar);
SELECT t.[DateColumn] 
FROM MyTable t
WHERE t.[DateColumn] >= @dateVar AND 
      t.[DateColumn] < @dateEnd;

Also from that article: using BETWEEN, DATEDIFF or CONVERT(CHAR(8)... are all slower.

MS SQL compare dates?

SELECT CASE WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) ...

Should do what you need.

Test Case

WITH dates(date1, date2, date3, date4)
     AS (SELECT CAST('20101231 15:13:48.593' AS DATETIME),
                CAST('20101231 00:00:00.000' AS DATETIME),
                CAST('20101231 15:13:48.593' AS DATETIME),
                CAST('20101231 00:00:00.000' AS DATETIME))
SELECT CASE
         WHEN CAST(date1 AS DATE) <= CAST(date2 AS DATE) THEN 'Y'
         ELSE 'N'
       END AS COMPARISON_WITH_CAST,
       CASE
         WHEN date3 <= date4 THEN 'Y'
         ELSE 'N'
       END AS COMPARISON_WITHOUT_CAST
FROM   dates 

Returns

COMPARISON_WITH_CAST   |  COMPARISON_WITHOUT_CAST
Y                         N

Query comparing dates in SQL

Instead of '2013-04-12' whose meaning depends on the local culture, use '20130412' which is recognized as the culture invariant format.

If you want to compare with December 4^th, you should write '20131204'. If you want to compare with April 12^th, you should write '20130412'.

The article Write International Transact-SQL Statements from SQL Server's documentation explains how to write statements that are culture invariant:

Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers, should use the unseparated numeric strings. For example, yyyymmdd as 19980924.

EDIT

Since you are using ADO, the best option is to parameterize the query and pass the date value as a date parameter. This way you avoid the format issue entirely and gain the performance benefits of parameterized queries as well.

UPDATE

To use the the the ISO 8601 format in a literal, all elements must be specified. To quote from the ISO 8601 section of datetime's documentation

To use the ISO 8601 format, you must specify each element in the format. This also includes the T, the colons (:), and the period (.) that are shown in the format.

... the fraction of second component is optional. The time component is specified in the 24-hour format.

Best way to compare dates without time in SQL Server

Don't use convert - that involves strings for no reason. A trick is that a datetime is actually a numeric, and the days is the integer part (time is the decimal fraction); hence the day is the FLOOR of the value: this is then just math, not strings - much faster

declare @when datetime = GETUTCDATE()
select @when -- date + time
declare @day datetime = CAST(FLOOR(CAST(@when as float)) as datetime)
select @day -- date only

In your case, no need to convert back to datetime; and using a range allows the most efficent comparisons (especially if indexed):

declare @when datetime = 'Feb 15 2012  7:00:00:000PM'
declare @min datetime = FLOOR(CAST(@when as float))
declare @max datetime = DATEADD(day, 1, @min)

select * from sampleTable where DateCreated >= @min and DateCreated < @max

SQL Server Current Date compare with specific date

If PostDate is DATE or DATETIME, instead of casting you could use DATEDIFF function to get the days between two dates and do the INT comparison:

WHERE DATEDIFF(DAY, PostDate, GETDATE())>2

If PostDate is varchar, stored in the format shown in the OP:

SET LANGUAGE british

SELECT ....
WHERE DATEDIFF(DAY, CAST(PostDate as datetime), GETDATE())>2

EDIT: Apparently DATEDIFF will work if PostDate is VARCHAR data type as well

DECLARE @PostDate VARCHAR(50)

SET @PostDate='08-01-2017'

SELECT DATEDIFF(DAY, @PostDate, GETDATE()) -- GETDATE() is 08-08-2017

-- Returns 7

Having said this, it is a good practice to keep Dates and Times as proper data types. In your case, you could change the data type to DATE, if possible. Will speed up lookups

EDIT 2: Please note, SQL Server works with ISO 8601 Date Format, which is YYYY-MM-DD, but the dates in OP's example, even though as per OP refer to dates in August 2017, are given incorrectly (referring to Jan and Feb 2017) and are stored as varchar. For correct results, these need to be either converted to DATE/DATETIME data type, or reformatted with the correct ISO format.

EDIT 3: Showing an example of casting OP's date format into proper, ISO format before calling DATEDIFF:

SET LANGUAGE british

DECLARE @PostDate VARCHAR(50)

SET @PostDate='2017-01-08'

SELECT DATEDIFF(DAY, CAST(@PostDate AS DATETIME), GETDATE()) -- GETDATE() is 08-08-2017

-- Returns 7

And the WHERE clause would be as follows:

-- In the begining of the select statement

SET LANGUAGE british

SELECT *
FROM ...
WHERE DATEDIFF(DAY, CAST(PostDate as datetime), GETDATE())>2

SQL datetime compare

Even though in Europe and everywhere in the world it makes perfect sense to use the month as the second of three items in the date. In the US and in this case, apparently, SQL's date time is MM.DD.YYYY, so you're going for the 15th month and 18th month

Therefore you should use

string query "SELECT * FROM LocalHotels WHERE city='LONDON' AND start <='12.15.2015 00:00:00' AND deadline >='12.18.2015 00:00:00' ORDER BY city"

or

string query "SELECT * FROM LocalHotels WHERE city='LONDON' AND start <='2015-12-15' AND deadline >='2015-12-18' ORDER BY city"

Compare data between two date ranges in SQL Server

The first thing I would do is to create common table expressions for your date ranges, each having two columns: a column with the data value, and a column with row number value. This way, It's going to be very easy to join by n-th value.

So here is my suggested solution:

First, Create and populate sample table (Please save us this step in your future questions)

DECLARE @data As Table
(
    [date] DATE,
    [value] INT
);

INSERT INTO @data
VALUES
('2018-01-01', 3),
('2018-01-02', 5),
('2018-01-03', 8),
('2018-01-04', 6),
('2018-02-04', 4),
('2018-02-05', 2),
('2018-02-06', 7),
('2018-02-07', 0);

Now, I've changed your @currentStartDateTime from 2018-02-04 to 2018-02-03,
to make sure I also return rows that are not in the table (Please make sure your sample data covers all requierments)

DECLARE @currentStartDateTime  datetime   = '2018-02-03 00:00:00',
        @currentEndDateTime    datetime   = '2018-02-07 23:59:59',
        @previousStartDateTime datetime   = '2018-01-01 00:00:00',
        @previousEndDateTime   datetime   = '2018-01-03 23:59:59'

Now, my solution seems quite cumbersome because I wanted to show all steps in details.
You might be able to simplify it.

First, calculate the max date difference in days.

Then, create a numbers cte from 1 to that difference + 1.

Then, create calendar ctes for each range,

Then a final cte to do a full join between the ranges,

and a select from that final cte left joined twice to the data table.

-- This allows us to use the smallest possible tally cte.
DECLARE @MaxDateDiff int;
SELECT @MaxDateDiff = MAX(d)
FROM (
    VALUES  (DATEDIFF(DAY, @currentStartDateTime, @currentEndDateTime)), 
            (DATEDIFF(DAY, @previousStartDateTime, @previousEndDateTime))
    ) v(d) -- I like table value constructors :-)

;WITH Tally AS 
(
    SELECT TOP (@MaxDateDiff + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) As Number
    FROM sys.objects a
    -- if your database is very small (number of tables, procedures ect'), 
    -- you might want to unremark the next row
    -- CROSS JOIN sys.objects b
),
CurrentRange AS  
(
    SELECT DATEADD(DAY, Number-1, @currentStartDateTime) As [Date], Number
    FROM Tally
    -- we need the where clause in case the other range is bigger...
    WHERE DATEADD(DAY, Number-1, @currentStartDateTime) <= @currentEndDateTime
), 
PreviousRange AS 
(
    SELECT DATEADD(DAY, Number-1, @previousStartDateTime) As [Date], Number
    FROM Tally
    WHERE DATEADD(DAY, Number-1, @previousStartDateTime) <= @previousEndDateTime
), 
BothRanges AS 
(        
    SELECT C.Date As CurDate, P.Date As PreDate
    FROM CurrentRange As C
    FULL JOIN PreviousRange As P ON C.Number =  P.Number
)

SELECT CurDate, ISNULL(c.Value, 0) as CurValue, PreDate, ISNULL(p.Value, 0) as PreValue
FROM BothRanges
LEFT JOIN @data AS c ON CurDate = c.[Date]
LEFT JOIN @data AS p ON PreDate = p.[Date]

Results: (Remember that @currentStartDateTime is different than the one on the question)

CurDate                 CurValue    PreDate                 PreValue
03.02.2018 00:00:00     0           01.01.2018 00:00:00     3
04.02.2018 00:00:00     4           02.01.2018 00:00:00     5
05.02.2018 00:00:00     2           03.01.2018 00:00:00     8
06.02.2018 00:00:00     7           NULL                    0
07.02.2018 00:00:00     0           NULL                    0

You can see a live demo on rextester.

SQL Server date comparisons based on month and year only

To handle inequalities, such as between, I like to convert date/times to a YYYYMM representation, either as a string or an integer. For this example:

DECLARE @date1 DATETIME = CAST('6/14/2014' AS DATETIME),
        @date2 DATETIME = CAST('6/15/2014' AS DATETIME),
        @date3 DATETIME = CAST('7/1/2014' AS DATETIME);

SELECT * FROM tableName WHERE @date2 BETWEEN @date1 AND @date3;

I would write the query as:

SELECT *
FROM tableName
WHERE year(@date2) * 100 + month(@date2) BETWEEN year(@date1) * 100 + month(@date1) AND
                                                 year(@date3) * 100 + month(@date1);

---

Related Topics