Calculate time difference in T-SQL / SQL Server

access_time 2 years ago visibility35 comment 0

This code snippet shows how to calculate time differences.

In SQL Server, DATEDIFF function can be used to find the differences between two dates/timestamps.

The syntax is:

DATEDIFF ( datepart , startdate , enddate )  

For parameter datepart, refer to the following table:

DatepartAbbrevations
yearyy,yyyy
quarterqq,q
monthmm,m
dayofyeardy,y
daydd,d
weekwk,ww
hourhh
minutemi,n
secondss,s
millisecond
ms
microsecond
mcs
nanosecond
ns

Code snippet

SELECT DATEDIFF(m,CAST('20171015' AS DATE), CAST('20191015' AS DATE)) AS Diff_Months;
SELECT DATEDIFF(d,CAST('20171015' AS DATE), CAST('20191015' AS DATE)) AS Diff_Days;
SELECT DATEDIFF(ww,CAST('20171015' AS DATE), CAST('20191015' AS DATE)) AS Diff_Weeks;
SELECT DATEDIFF(HH,CAST('20171015' AS DATE), CAST('20191015' AS DATE)) AS Diff_Hours;
info Last modified by Raymond 2 years ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

visibility 41
thumb_up 2
access_time 2 years ago

In different databases, the syntax of selecting top N records are slightly different. They may also differ from ISO standards.

visibility 4462
thumb_up 0
access_time 6 years ago

For Teradata developers, if you have no SQL Server installed, please go to the following link to download the SQL Server 2014 Expression Edition. http://www.microsoft.com/en-us/server-cloud/Products/sql-server-editions/sql-server-express.aspx It is easy to get started and free to use.

visibility 40
thumb_up 0
access_time 2 years ago

In Oracle database, you can select from dual table if you only want to return a one row result set. In many other databases, the query engine supports select directly from constant values without specifying a table name.