SQL - Built-in Functions - Date and Time Functions - DATEDIFF - Subtract dates in SQL
|
Mart 11, 2011 by Microsoft
|
|
Article Information
This topic contains and tries to cover following subjects
- Explanation of DATEDIFF() function in SQL
- Syntax of DATEDIFF() function in SQL
- Example to DATEDIFF() function in SQL
Articles contains and tries to provide answer to following questions and issues:
- What is DATEDIFF() function?
- How to use DATEDIFF() function?
- How to subtract dates in SQL?
- How to subtract a date from another date with SELECT query?
Articles may require following knowledge:
- Basic query flow in SQL
- SQL Server Management Studio
- QL server date functions
Explanation of DATEDIFF() function in SQL
DATEDIFF function can be used to find difference between two dates. To be more specific, we can use that function to subtract two date to find how many year, month or day exist between two dates. For example, assume that we have a column that indicates user log-in dates. In a admin panel, we want to indicate when he or she is last logged in to the panel. Datediff can be used in such scenarios.
Syntax of DATEDIFF() function in SQL
In order subtract two date with datediff, we need to provide which portion of date we need to apply subtraction first. Second parameter is start date, last is end date.
DATEDIFF ( - date portion - , - start date - , - end date -)
Date portion accepts following parameters:
year
quarter
month
dayofyear
day
week
hour
minute
second
millisecond
microsecond
nanosecond
For example, if we use "week" as parameter, it finds how many week exist between two date. With that function, it is easier to find how many week, day, hour, minute, nanosecond between two date - time.
Example to USER_NAME() function in SQL
To demonstrate how to user DATEDIFF function, we will use it in SELECT query. Result displays how many week exist between the two dates which are provided as parameter to the query.
Query is as follows:
As query result indicates, there are 720 weeks between two date.
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | http://msdn.microsoft.com/en-us/library/ms189794.aspx |
| Loc: | articles |
| Tags: | tsql |
|