DateDiff function, datediff

DateDiff function, datediff
In MySQL, you can use the DATEDIFF () function to calculate the number of days between two dates.

Syntax:

datediff(date1,date2)

Note: date1 and date2 must be valid date or date/time expressions

Example 1

SELECT DATEDIFF('2010-6-30','2010-6-26') AS DiffDate

Example 2

SELECT DATEDIFF('20140618145239','20140613125249') AS DiffDate

In oracle, how does one calculate the number of days between two dates without the DateDiff function?

In oracle, date types can be directly compared

select to_date('2014-05-05','yyyy-mm-dd') - to_date('2014-05-02','yyyy-mm-dd') from dual

In this way, two days of time difference can be calculated.

If it is a timestamp

SELECT to_date('20140322154628', 'yyyymmddhh24miss') - to_date('20140320114631', 'yyyymmddhh24miss')from dual 

You can also calculate the number of days for the difference, but there are decimals, so you can use floor

select floor(to_date('20140322154628', 'yyyymmddhh24miss') - to_date('20140320114631', 'yyyymmddhh24miss')) from dual; 

Datediff function usage

DateDiff: SQL server Function
Returns the value of Variant (Long), indicating the number of time intervals between two specified days.
Syntax
DateDiff (interval, date1, date2 [, firstdayofweek [, firstweekofyear])
The DateDiff function syntax contains the following naming parameters:
Partial description
Interval is required. String expression used to calculate the time difference between date1 and date2
Date1 □date2 is required; Variant (Date ). The two dates used in the calculation.
Firstdayofweek is optional. Specifies the constant of the first day of a week. If not specified, Sunday is the first day.
Firstweekofyear is optional. Specifies the constant of the first week of a year. If this parameter is not specified, the week that contains April 1 is the first week.
Set
The interval parameter settings are as follows:
Set description
Yyyy
Q quarter
M month
Y number of days in a year
D
Number of days per week
Ww week
H hour
N minutes
S seconds
The firstdayofweek parameter is set as follows:
Constant Value description
VbUseSystem 0 is set using nls api.
VbSunday 1 Sunday (default)
VbMonday 2 Monday
VbTuesday 3 Tuesday
VbWednesday 4
VbThursday 5 Thursday
VbFriday 6 Friday
VbSaturday 7 Saturday
Constant Value description
VbUseSystem 0 is set using nls api.
VbFirstJan1 1 starts from the week of July 1, January 1 (default ).
VbFirstFourDays 2 starts from the first half of its week in the week of the New Year.
VbFirstFullWeek 3 starts from the week of the first non-Cross-Year Plan.
Description
The DateDiff function can be used to determine the specified time interval between two dates. For example, you can use DateDiff to calculate the number of days between two dates, or calculate the number of weeks from today to the end of the year.
To calculate the number of days in which date1 differs from date2, you can use "day of the year" (y) or "day" (d ). When interval is "the number of days in a week" (w), DateDiff returns the number of weeks in the two-day period. If date1 is Monday, DateDiff calculates the number of Monday until date2. This number contains date2 but not date1. However, if interval is "Week" (ww), The DateDiff function returns the "calendar week" number for the two-day period. Calculated based on the number of Sundays between date1 and date2. If date2 is just Sunday, date2 will also be added to the DateDiff counting result. However, no matter whether date1 is Sunday or not, it will not be included.
If date1 is later than date2, the return value of the DateDiff function is negative.
The firstdayofweek parameter affects the calculation result using the time interval symbol "W" or "WW.
If date1 or date2 is a date text, the specified year becomes a fixed part of the date. However, if date1 or date2 is enclosed by double quotation marks (""), and the year is omitted, the full text will be included in each calculation expression date1 or date...>

What is the datediff function in SQL database?

DateDiff usage in SQL

DATEDIFF
Returns the number of date and time boundaries that span two specified dates.

Syntax
DATEDIFF (datepart, startdate, enddate)

Parameters
Datepart

Is a parameter that specifies which part of the date should be used to calculate the difference. The following table lists Microsoft®SQL Server™Recognize the date and abbreviation.

Abbreviation of date
Year yy, yyyy
Quarter qq, q
Month mm, m
Dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
Minute mi, n
Second ss, s
Millisecond MS

Startdate

Is the start date of the calculation. Startdate is an expression that returns the datetime, smalldatetime, or date format string.

Because smalldatetime is accurate to minutes, when smalldatetime is used, the second and millisecond are always 0.

If you only specify the last two digits of the year, the number of the last two digits smaller than or equal to the value of the "two-digit year deadline" configuration option is in the same century as the end year. The last two-digit number greater than the value of this option is in the century before the end of the century. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049,2050 as 1950. To avoid blurring, use a four-digit year.

For more information about the time value, see time format. For more information about the specified date, see datetime and smalldatetime.

Enddate

Is the end date of the calculation. Enddate is an expression that returns the datetime, smalldatetime, or date format string.

Return type
Integer

Note
Startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.

When the result exceeds the Integer Range, DATEDIFF produces an error. The maximum number of milliseconds is 24 days, 20 hours, 31 minutes, And 23.647 seconds. The maximum number of seconds is 68 years.

The cross-minute, second, and millisecond Boundary Calculation method makes the results given by DATEDIFF consistent in all data types. The result is an integer with positive and negative numbers, which is equal to the number of datepart boundaries between the first and second days. For example, the number of weeks between January 4 (Sunday) and January 11 (Sunday) is 1.

Example
This example determines the title publication date and the number of days in the current day in the pubs database.

USE pubs
GO
Select datediff (day, pubdate, getdate () AS no_of_days
FROM titles
GO

Thank you!