SQL Server Days Function: Your Guide to Efficient Date Handling

Basic Date Functions

GETDATE()

Returns the current date and time

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

DATEADD()

Adds or subtracts a specified time interval

-- 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

DATEDIFF()

Calculates the difference between two dates

-- 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

Date Parts Extraction

DATEPART()

Extracts specific parts of a date

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

YEAR(), MONTH(), DAY()

Shortcuts for common date parts

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

Date Formatting

FORMAT()

Formats dates using standard or custom patterns

-- 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

CONVERT()

Converts dates to different formats using style codes

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

Practical Examples

First and Last Day of Month

-- 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

Business Days Calculation

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

Age Calculation

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

Best Practices

Use Appropriate Data Types
- Use datetime2 for new development (more precise, larger date range)
- Use date when you only need the date portion
- Use time when you only need the time portion
Index Optimization
- Avoid using functions on indexed columns in WHERE clauses
- Instead of: WHERE YEAR(DateColumn) = 2024
- Use: WHERE DateColumn >= '2024-01-01' AND DateColumn < '2025-01-01'
Time Zone Handling
- Store dates in UTC using GETUTCDATE()
- Convert to local time only for display
- Consider using DATETIMEOFFSET for time zone awareness
Performance Tips
- Cache date calculations when possible
- Use computed columns for frequently accessed date parts
- Consider partitioning large tables by date ranges

Common Pitfalls to Avoid

1. Implicit Conversions

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

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

2. Function Usage in WHERE Clauses

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

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

3. Time Zone Issues

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

Remember to test these functions with your specific use case and data volume to ensure optimal performance.

Read More Topics

Basic SQL knowledge for programmer and developers

PostgreSQL cheat sheet

SQL interview questions for freshers