What can be done using the current date or 1st Jan, 0001 AD as the reference date?

 In SQL Server, you can perform various operations using the current date or 1st Jan, 0001 AD (the minimum possible date in SQL Server, represented as DATETIME value 0001-01-01 00:00:00.000) as a reference date. Here are some ideas:

---

1. Calculate Days Between Two Dates

You can calculate the number of days between the current date and a reference date:

SELECT DATEDIFF(DAY, '0001-01-01', GETDATE()) AS DaysSinceReference;

---

2. Generate Sequential Dates

Generate dates from the reference date:

WITH DateSequence AS (
    SELECT CAST('0001-01-01' AS DATETIME) AS ReferenceDate
    UNION ALL
    SELECT DATEADD(DAY, 1, ReferenceDate)
    FROM DateSequence
    WHERE ReferenceDate < GETDATE()
)
SELECT ReferenceDate
FROM DateSequence
OPTION (MAXRECURSION 0);

---

3. Find Difference in Various Units

You can find the difference in other units (years, months, seconds):

SELECT 
    DATEDIFF(YEAR, '0001-01-01', GETDATE()) AS YearsSinceReference,
    DATEDIFF(MONTH, '0001-01-01', GETDATE()) AS MonthsSinceReference,
    DATEDIFF(SECOND, '0001-01-01', GETDATE()) AS SecondsSinceReference;

---

4. Determine Future or Past Dates

Calculate a date relative to the reference:

-- 1000 days after the reference date
SELECT DATEADD(DAY, 1000, '0001-01-01') AS FutureDate;

-- 1000 days before the current date
SELECT DATEADD(DAY, -1000, GETDATE()) AS PastDate;

---

5. Age Calculation

Calculate age using the reference date:

DECLARE @ReferenceDate DATE = '2000-01-01';
SELECT DATEDIFF(YEAR, @ReferenceDate, GETDATE()) 
       - CASE WHEN MONTH(@ReferenceDate) > MONTH(GETDATE()) 
              OR (MONTH(@ReferenceDate) = MONTH(GETDATE()) AND DAY(@ReferenceDate) > DAY(GETDATE())) 
              THEN 1 ELSE 0 END AS Age;

---

6. Format Reference Date

Display the reference date in different formats:

SELECT FORMAT('0001-01-01', 'dd/MM/yyyy') AS FormattedReferenceDate;

---

7. Find Day of the Week

Get the day of the week for the reference date:

SELECT DATENAME(WEEKDAY, '0001-01-01') AS DayOfWeek;

---

8. Elapsed Time

Break down the elapsed time into years, months, and days:

DECLARE @ReferenceDate DATETIME = '0001-01-01';
SELECT 
    DATEDIFF(YEAR, @ReferenceDate, GETDATE()) AS YearsElapsed,
    DATEDIFF(MONTH, @ReferenceDate, GETDATE()) % 12 AS MonthsElapsed,
    DATEDIFF(DAY, DATEADD(MONTH, DATEDIFF(MONTH, @ReferenceDate, GETDATE()), @ReferenceDate), GETDATE()) AS DaysElapsed;

---

9. Find Next Occurrence

Calculate the next occurrence of a specific day:

SELECT DATEADD(DAY, (7 - DATEDIFF(DAY, '0001-01-01', GETDATE()) % 7), GETDATE()) AS NextMonday;

---

These examples demonstrate how you can use SQL Server's date and time functions with reference dates like the current date or 0001-01-01.