Comparing Two Dates in a SQL Request

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.

Compare dates from two queries PL/SQL

You can enumerate the timestamps you want in a CTE, then bring the table with a left join:

with cte (reading_date) as (
    select date '2020-11-17' from dual
    union all
    select reading_date + interval '30' minute 
    from cte 
    where reading_date + interval '30' minute < date '2020-11-19'
)
select c.reading_date, d.reading_value
from cte c
left join dcm_reading d on d.reading_date = c.reading_date
order by c.reading_date

I like to use recursive queries rather than Oracle specific connect by syntax, because they are standard SQL - but that's mostly a matter of taste, the logic remains the same.

In SQL how to compare date values?

Nevermind found an answer. Ty the same for anyone who was willing to reply.

WHERE DATEDIFF(mydata,'2008-11-20') >=0;

How to compare dates between rows with same column values except one or two columns?

The window functions are your friends. Well worth your time to get comfortable with them. Also not clear if you need NAME in the partition by

Also, if you want to see ties ... use dense_rank() instead of row_number()

More Performant

Select *
 From  (
         Select *
               ,RN = row_number() over (partition by InvoiceNumber,Name order by PostingDate)
           From YourTable
       ) A
 Where RN=1

Another Option

Select top 1 with ties *
 From  YourTable 
 Order by row_number() over (partition by InvoiceNumber,Name order by PostingDate)

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.

---

Related Topics