SQL Server Days Function: Your Guide to Efficient Date Handling

SELECT GETDATE() AS CurrentDateTime
-- Returns: 2024-10-29 14:30:45.123

-- Add 7 days to current date
SELECT DATEADD(day, 7, GETDATE()) AS OneWeekFromNow

-- Subtract 1 month
SELECT DATEADD(month, -1, GETDATE()) AS OneMonthAgo

-- Common intervals: year, quarter, month, week, day, hour, minute, second

-- Calculate days between dates
SELECT DATEDIFF(day, '2024-01-01', '2024-12-31') AS DaysDifference
-- Returns: 365

-- Calculate years between dates
SELECT DATEDIFF(year, '2020-01-01', '2024-10-29') AS YearsDifference
-- Returns: 4

SELECT 
    DATEPART(year, GETDATE()) AS CurrentYear,
    DATEPART(month, GETDATE()) AS CurrentMonth,
    DATEPART(day, GETDATE()) AS CurrentDay,
    DATEPART(hour, GETDATE()) AS CurrentHour

SELECT 
    YEAR(GETDATE()) AS CurrentYear,
    MONTH(GETDATE()) AS CurrentMonth,
    DAY(GETDATE()) AS CurrentDay

-- Standard date format
SELECT FORMAT(GETDATE(), 'd') AS ShortDate        -- 10/29/2024
SELECT FORMAT(GETDATE(), 'D') AS LongDate         -- Tuesday, October 29, 2024
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy') AS Custom  -- 10/29/2024

SELECT 
    CONVERT(varchar, GETDATE(), 101) AS USFormat,     -- 10/29/2024
    CONVERT(varchar, GETDATE(), 103) AS BritishFormat -- 29/10/2024

-- First day of current month
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS FirstDayOfMonth

-- Last day of current month
SELECT DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0)) AS LastDayOfMonth

CREATE FUNCTION dbo.CalculateBusinessDays
(
    @StartDate datetime,
    @EndDate datetime
)
RETURNS int
AS
BEGIN
    DECLARE @Days int
    
    SELECT @Days = DATEDIFF(day, @StartDate, @EndDate) + 1
    
    -- Subtract weekends
    SELECT @Days = @Days - 
        (SELECT COUNT(*)
         FROM (
             SELECT TOP (DATEDIFF(day, @StartDate, @EndDate) + 1)
                 DATEADD(day, ROW_NUMBER() OVER (ORDER BY a.object_id) - 1, @StartDate) AS Date
             FROM sys.objects a
             CROSS JOIN sys.objects b
         ) AS Dates
         WHERE DATEPART(weekday, Date) IN (1, 7)) -- 1 = Sunday, 7 = Saturday
    
    RETURN @Days
END

CREATE FUNCTION dbo.CalculateAge
(
    @BirthDate datetime,
    @CurrentDate datetime
)
RETURNS int
AS
BEGIN
    RETURN YEAR(@CurrentDate) - YEAR(@BirthDate) -
        CASE
            WHEN (MONTH(@CurrentDate) < MONTH(@BirthDate)) OR 
                 (MONTH(@CurrentDate) = MONTH(@BirthDate) AND DAY(@CurrentDate) < DAY(@BirthDate))
            THEN 1
            ELSE 0
        END
END

-- Bad (implicit conversion)
WHERE DateColumn = '2024-10-29'

-- Good (explicit conversion)
WHERE DateColumn = CAST('2024-10-29' AS datetime2)

-- Bad (non-sargable)
WHERE DATEADD(day, -7, GETDATE()) <= OrderDate

-- Good (sargable)
WHERE OrderDate >= DATEADD(day, -7, GETDATE())

-- Consider time zones when comparing dates
WHERE OrderDate >= @StartDate AT TIME ZONE 'UTC'