DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Data
  4. Dates and Times in SQL Server: DATEDIFF() and DATEDIFF_BIG()

Dates and Times in SQL Server: DATEDIFF() and DATEDIFF_BIG()

Look at how to determine the difference between two datetime values in SQL Server using a couple different functions.

Randolph West user avatar by
Randolph West
·
Nov. 29, 18 · Tutorial
Like (3)
Save
Tweet
Share
18.04K Views

Join the DZone community and get the full member experience.

Join For Free

Last time we looked at adding or subtracting date parts using the DATEADD() T-SQL system function. This week we see how to calculate the difference between two date-time values using and DATEDIFF_BIG().

The syntax for both functions is identical:

DATEDIFF (datepart, startdate, enddate)
-- returns a result as an INT

DATEDIFF_BIG (datepart, startdate, enddate)
-- returns a result as a BIGINT

The only functional difference between them is that the DATEDIFF_BIG() returns values as a , for results that exceed the boundary of an INT. Keep this in mind when deciding which one to use. For example, the maximum number of seconds an INT can hold is 68 years, while a BIGINT can comfortably store the number of seconds in 10,000 years. This becomes especially important when dealing with microseconds and nanoseconds.

The rest of the post will use DATEDIFF() to refer to both functions.

The following table may look familiar to regular readers of this site. Here are the datepart options we can use in DATEDIFF(). While abbreviations are supported, it is better to use the full expression to ensure clarity in our code.

datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond

ns

Calculate the Difference in Seconds Between Two Dates

One of the most common scenarios is to calculate the number of seconds (or milliseconds or even nanoseconds) between two events. The code might look something like this (with demo data in the startdate and enddate parameters):

DECLARE @dt1 AS DATETIME2(7) = CAST('2018-11-14 09:05:12.2390114' AS DATETIME2(7));
DECLARE @dt2 AS DATETIME2(7) = CAST('2018-11-14 09:02:03.8440911' AS DATETIME2(7));
SELECT DATEDIFF(SECOND, @dt1, @dt2);

In this contrived example, the result is an integer with the value of -189, or 189 seconds. Note that if the input values were reversed, the result would be a positive number. In most scenarios, we would convert this to an absolute value to avoid negative amounts, by using the T-SQL system function.

Date Boundaries

The official documentation makes specific reference to datepart boundaries when using DATEDIFF(). In other words, we need to be aware of the behaviour when crossing these boundaries.

In the example provided (reproduced below), despite the start and end dates differing by only 100 nanoseconds, they all return a value of 1 because they cross a datepart boundary, moving from one year into the next (or quarter, month, day, hour, etc.).

SELECT DATEDIFF_BIG(year,        '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(quarter,     '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(month,       '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(dayofyear,   '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(day,         '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(week,        '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(hour,        '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(minute,      '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(second,      '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(millisecond, '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');

Other Considerations

Owing to the granularity of certain data types, rounding errors may occur. For example, the SMALLDATETIME data type is only accurate to the nearest minute, so any calculation using these as input parameters and expecting a number of seconds will round up or down to the nearest minute as well.

Another consideration is where no date or time is used, respectively. In other words, we are calculating the difference between two times. In such cases, the date 1900-01-01 will be prefixed to the time. For situations where no time is included in the input dates, a time of 00:00:00 will be substituted.

Share your use cases for DATEDIFF() and DATEDIFF_BIG() in the comments below.

Data Types sql code style Data (computing) Documentation Convert (command) POST (HTTP) Database

Published at DZone with permission of Randolph West, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Kubernetes vs Docker: Differences Explained
  • How To Check Docker Images for Vulnerabilities
  • ChatGPT Prompts for Agile Practitioners
  • Top 10 Secure Coding Practices Every Developer Should Know

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: