SQL Server Days Between Date and Today

In this comprehensive article, I’ll share the proven methods and optimization strategies I’ve developed through real-world implementations across different applications, helping you know date calculations while avoiding common errors that can impact performance and accuracy.

Table of Contents

SQL Server Days Between Date and Today

Before diving into specific methods, let me explain the fundamental concepts of date arithmetic in SQL Server and why accurate day calculations are critical for business applications.

SQL Server Date and Time Data Types

SQL Server provides several date and time data types, each optimized for different calculation scenarios.

Primary Date Types for Day Calculations:

Data Type	Storage Size	Range	Precision	Best Use Case
DATE	3 bytes	0001-01-01 to 9999-12-31	Day	Simple date calculations
DATETIME	8 bytes	1753-01-01 to 9999-12-31	3.33 milliseconds	Legacy applications
DATETIME2	6-8 bytes	0001-01-01 to 9999-12-31	100 nanoseconds	Modern applications
SMALLDATETIME	4 bytes	1900-01-01 to 2079-06-06	1 minute	Space-constrained scenarios

Current Date Functions in SQL Server:

GETDATE(): Returns current system date and time
SYSDATETIME(): Returns current system date and time with higher precision
GETUTCDATE(): Returns current UTC date and time
CURRENT_TIMESTAMP: ANSI SQL standard equivalent to GETDATE()

Method 1: Using DATEDIFF Function

The DATEDIFF function is the most commonly used and reliable method for calculating days between dates in SQL Server. This function provides precise, efficient calculations.

Basic DATEDIFF Syntax and Usage

-- Basic syntax: DATEDIFF(datepart, startdate, enddate)
SELECT DATEDIFF(DAY, date_column, GETDATE()) AS days_difference
FROM your_table;

-- Example:
SELECT 
    sale_date,
    sale_amount,
    DATEDIFF(DAY, sale_date, GETDATE()) AS sale_days
FROM SALES
WHERE sale_amount = 500;

After executing the above query, I got the expected output as shown in the screenshot below.

Practical Business Applications

Customer Relationship Management:

-- Calculate days since last customer interaction
SELECT 
    c.customer_id,
    c.company_name,
    c.last_contact_date,
    DATEDIFF(DAY, c.last_contact_date, GETDATE()) AS days_since_contact,
    CASE 
        WHEN DATEDIFF(DAY, c.last_contact_date, GETDATE()) <= 30 THEN 'Recent'
        WHEN DATEDIFF(DAY, c.last_contact_date, GETDATE()) <= 90 THEN 'Moderate'
        WHEN DATEDIFF(DAY, c.last_contact_date, GETDATE()) <= 180 THEN 'Stale'
        ELSE 'Critical'
    END AS contact_status
FROM ncustomers c
WHERE c.region = 'USA'
ORDER BY days_since_contact DESC;

After executing the above query, I got the expected output as shown in the screenshot below.

Financial and Accounting Applications:

-- Calculate invoice aging
SELECT 
    invoice_number,
    customer_name,
    invoice_date,
    due_date,
    invoice_amount,
    DATEDIFF(DAY, due_date, GETDATE()) AS days_overdue,
    CASE 
        WHEN DATEDIFF(DAY, due_date, GETDATE()) <= 0 THEN 'Current'
        WHEN DATEDIFF(DAY, due_date, GETDATE()) <= 30 THEN '1-30 Days'
        WHEN DATEDIFF(DAY, due_date, GETDATE()) <= 60 THEN '31-60 Days'
        WHEN DATEDIFF(DAY, due_date, GETDATE()) <= 90 THEN '61-90 Days'
        ELSE 'Over 90 Days'
    END AS aging_category
FROM ninvoices
WHERE status = 'PENDING'
ORDER BY days_overdue DESC;

After executing the above query, I got the expected output as shown in the screenshot below.

Advanced DATEDIFF Techniques

Handling Negative Values:

-- Calculate absolute days difference regardless of date order
SELECT 
    order_id,
    promised_delivery_date,
    actual_delivery_date,
    ABS(DATEDIFF(DAY, promised_delivery_date, actual_delivery_date)) AS delivery_variance_days,
    CASE 
        WHEN DATEDIFF(DAY, promised_delivery_date, actual_delivery_date) < 0 THEN 'Early'
        WHEN DATEDIFF(DAY, promised_delivery_date, actual_delivery_date) = 0 THEN 'On Time'
        ELSE 'Late'
    END AS delivery_status
FROM orders
WHERE shipping_region = 'United States';

Method 2: Date Arithmetic Operations

SQL Server also supports direct arithmetic operations on date values, providing an alternative approach for calculating day differences that can be useful in specific scenarios.

Basic Date Arithmetic

We can execute the below SQL query to do the direct subtraction that returns the difference in days.

-- Direct subtraction returns difference in days
SELECT 
    employee_id,
    full_name,
    hire_date,
    CAST(GETDATE() - CAST(hire_date AS DATETIME) AS INT) AS employment_days
FROM nemployees
WHERE office_location IN ('New York, NY', 'Chicago, IL', 'Austin', 'Houston');

After executing the above query, I got the expected output as shown in the screenshot below.

calculate days between two dates in sql query without datediff

Calculate business metrics using arithmetic using the below query.

-- Calculate business metrics using arithmetic
SELECT 
    project_id,
    project_name,
    start_date,
    target_completion_date,
    CAST(GETDATE() - CAST(start_date AS DATETIME) AS INT) AS project_duration_days,
	CAST(GETDATE() - CAST(target_completion_date AS DATETIME) AS INT) AS days_until_deadline
FROM nproject
WHERE project_manager_location LIKE '%Boston%'
    AND status IN ('In Progress', 'Planning');

After executing the above query, I got the expected output as shown in the screenshot below.

calculate days between two dates in sql developer

Combining Arithmetic with Business Logic

Employee Tenure and Benefits Calculations:

-- Calculate employee tenure for US-based workforce
SELECT 
    emp.employee_id,
    emp.full_name,
    emp.hire_date,
    emp.department,
    CAST(GETDATE() - emp.hire_date AS INT) AS tenure_days,
    CAST((GETDATE() - emp.hire_date) / 365.25 AS DECIMAL(10,2)) AS tenure_years,
    CASE 
        WHEN CAST(GETDATE() - emp.hire_date AS INT) >= 365 THEN 'Eligible for Benefits'
        ELSE 'Not Yet Eligible'
    END AS benefits_eligibility,
    CASE 
        WHEN CAST(GETDATE() - emp.hire_date AS INT) >= 1825 THEN '4+ Weeks PTO' -- 5 years
        WHEN CAST(GETDATE() - emp.hire_date AS INT) >= 1095 THEN '3+ Weeks PTO' -- 3 years
        WHEN CAST(GETDATE() - emp.hire_date AS INT) >= 365 THEN '2+ Weeks PTO'  -- 1 year
        ELSE 'Accruing PTO'
    END AS vacation_tier
FROM employees emp
WHERE emp.work_location_country = 'USA'
ORDER BY tenure_days DESC;

Method 3: Using CAST and CONVERT Functions

For scenarios requiring specific date formatting or precision control, combining CAST/CONVERT functions with date arithmetic provides additional flexibility.

Precision Control and Formatting

-- Calculate days with specific precision and formatting
SELECT 
    subscription_id,
    customer_name,
    subscription_start_date,
    CAST(DATEDIFF(DAY, subscription_start_date, GETDATE()) AS VARCHAR(10)) + ' days' AS subscription_age,
    CONVERT(VARCHAR(20), subscription_start_date, 101) AS formatted_start_date, -- MM/DD/YYYY
    CASE 
        WHEN DATEDIFF(DAY, subscription_start_date, GETDATE()) <= 30 THEN 'New Subscriber'
        WHEN DATEDIFF(DAY, subscription_start_date, GETDATE()) <= 365 THEN 'Regular Subscriber'
        ELSE 'Long-term Subscriber'
    END AS subscriber_category
FROM subscriptions
WHERE billing_country = 'United States'
    AND status = 'Active';

After executing the above query, I got the expected output as shown in the screenshot below.

Business Intelligence Applications

Sales Performance Metrics:

-- Calculate sales performance metrics with date conversions
SELECT 
    sales_rep_id,
    rep_name,
    territory,
    last_sale_date,
    CONVERT(VARCHAR(10), DATEDIFF(DAY, last_sale_date, GETDATE())) AS days_since_last_sale,
    CAST(
        CASE 
            WHEN DATEDIFF(DAY, last_sale_date, GETDATE()) = 0 THEN 'Today'
            WHEN DATEDIFF(DAY, last_sale_date, GETDATE()) = 1 THEN 'Yesterday'
            ELSE CAST(DATEDIFF(DAY, last_sale_date, GETDATE()) AS VARCHAR(10)) + ' days ago'
        END AS VARCHAR(20)
    ) AS friendly_last_sale,
    CASE 
        WHEN DATEDIFF(DAY, last_sale_date, GETDATE()) <= 7 THEN 'Excellent'
        WHEN DATEDIFF(DAY, last_sale_date, GETDATE()) <= 30 THEN 'Good'
        WHEN DATEDIFF(DAY, last_sale_date, GETDATE()) <= 60 THEN 'Needs Attention'
        ELSE 'Critical'
    END AS performance_status
FROM sales_representatives
WHERE territory_country = 'USA'
ORDER BY DATEDIFF(DAY, last_sale_date, GETDATE());

Knowing the calculation of days between dates and today is fundamental to building robust, efficient database applications.

Method Selection Summary:

Method	Performance	Complexity	Maintenance	Best For
DATEDIFF Function	Excellent	Low	Easy	General business calculations
Date Arithmetic	Very Good	Low	Easy	Simple day differences
CAST/CONVERT Methods	Good	Medium	Moderate	Formatted reporting

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.