Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • SQL Server - Get Started
  • Install SQL Server
  • SQL Server Management Studio
  • SQL Server - Windows Authentication
  • SQL Server - Authentication
  • SQL Server - Create New User
  • SQL Server - GRANT/REVOKE Permissions to User
  • SQL Server - Data Types
  • SQL Server - Naming Conventions
  • SQL Server - CREATE Database
  • SQL Server - CREATE Table
  • Add Columns
  • Identity Column
  • Rename Column, Table
  • Drop Columns
  • SQL Server - Schema
  • SQL Server - Tables Relations
  • SQL Server - Primary Keys
  • Modify/Delete Primary Keys
  • SQL Server - Foreign Keys
  • Modify/Delete Foreign Keys
  • SQL Server - Check Constraints
  • SQL Server - Unique Constraints
  • SQL Server - Views
  • Modify/Delete Views
  • SQL Server - Functions
  • SQL Server - Stored Procedures
  • Stored Procedure Parameters
  • SQL Server - Indexes
  • Non-clustered Indexes
  • Modify/Delete Indexes
  • SQL Server - Triggers
  • DDL Triggers
  • LOGON Triggers
  • Enable/Disable Triggers
  • Modify/Delete Triggers
  • SQL Server - Sequence
  • SQL Server - Synonyms
  • SQL Server - IF ELSE Statement
  • SQL Server - Loops
  • SQL Server - Insert Data
  • SQL Server - Update Data
  • SQL Server - Delete Data
  • SQL Server - Select Query
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • SQL Server - Inner Join
  • Left Join
  • Right Join
  • Full Join
  • Self Join
  • Dynamic SQL
  • Built-in Functions
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

SQL Server DATEDIFF() Function

In SQL Server, the DATEDIFF() function returns the difference between the specified start date and end date in integer. It can be difference between days, months, weeks, hours, seconds based on the passed datepart parameter.

Syntax:

DATEDIFF(datepart, startdate, enddate)

Parameters

datepart: It is the part of the date like day, month, year, week, etc. It is the unit in which the DATEDIFF() function returns the difference between a start date and an end date e.g. it returns the difference in days if datepart is day.

DatepartDatepart abbreviation
yeary, yy, yyyy
quarterqq, q
monthmm, m
dayofyeardy
daydd, d
weekwk, ww
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns

startdate: The starting datetime value.

enddate: The ending datetime value.

Difference will be calculated as startdate - enddate. startdate and enddate have to be in a format that can be resolved to types DATE, DATETIME, TIME, SMALLDATETIME, DATETIMEOFFSET.

Return Value

Returns an integer value, which is the difference between the specified start date and the end date.

Returns an error if the result is out of range for an integer (-2,147,483,648 to +2,147,483,647). Use the DATEDIFF_BIG() function to handle large difference between startdate and enddate values.

DATEDIFF() returns zero if both the start date and the end date are time values and the datepart is not a time datepart.

DATEDIFF() uses the time zone offset component of the startdate or enddate to calculate the return value.

Get Difference between Dates in Days

Example: DATEDIFF()
SELECT DATEDIFF(day, '01/10/2022 4:23:00', '01/11/2022 8:23:00') AS ReturnDate

In the below example, the startdate is bigger than the enddate, and so the DATEDIFF() function returns a negative value.

Example: DATEDIFF()
SELECT DATEDIFF(day, '12/23/22', '01/11/2022') AS ReturnDate

Use DATEDIFF() with Column

In the following example, DATEDIFF() function is used with the HireDate column of the Employee table and the GETDATE() function which returns a current datetime value. It returns the time elapsed in months from the hire date of the employee along with EmployeeId and FirstName.

Example: DATEDIFF() with Column
SELECT EmployeeID, FirstName, DATEDIFF(mm, HireDate, GETDATE()) AS TimeInMonths
FROM Employee

Get the Difference between Times

The DATEDIFF() function can also return the difference between the specified time values when datepart parameter is HH, MI, and SS.

Example: DATEDIFF() with Time Values
select datediff(HH,'3:22:59','4:23:50') as HoursDiff, 
datediff(MI,'3:20:59','4:23:50') as MinsDiff,
datediff(SS,'4:22:59', '4:23:50') as SecDiff;
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.