In SQL Server, how do I relate each date in a set to the subsequent date from the same set? I have a large table with a DATETIME2 column. I would like to write a query that relates each distinct date in that column to the date from the same column that immediately follows the first date. I suspect that I need to use a self-join, but I'm unsure about what to do next. How do I write such a query?
I don't have any preferences about how the case of the last date is handled. Its NextDate can be set to NULL or the row can be excluded.
Example:
DROP TABLE IF EXISTS #Dates

CREATE TABLE #Dates (
    MyDate DATETIME2 NOT NULL
)

INSERT INTO #Dates (MyDate) VALUES ('20210101')
INSERT INTO #Dates (MyDate) VALUES ('20210102')
INSERT INTO #Dates (MyDate) VALUES ('20210103')
INSERT INTO #Dates (MyDate) VALUES ('20210104')
INSERT INTO #Dates (MyDate) VALUES ('20210105')

SELECT 
    MyDate AS CurrentDate
    -- , ??? AS NextDate
FROM #Dates
ORDER BY CurrentDate

How do I get:
+-------------+-------------+
| CurrentDate |  NextDate   |
+-------------+-------------+
|    20210101 |    20210102 |
|    20210102 |    20210103 |
|    20210103 |    20210104 |
|    20210104 |    20210105 |
|    20210105 |    NULL     |
+-------------+-------------+
Unless you're on a very old version of SQL Server, the LEAD function should work.
SELECT 
    MyDate AS CurrentDate,
    LEAD(MyDate, 1, NULL) OVER
    (
        ORDER BY
            MyDate
    ) AS NextDate
FROM #Dates
ORDER BY CurrentDate;