SQL SERVER: Get total days between two dates
PRINT DATEDIFF(DAY, '1/1/2011', '3/1/2011')
will give you what you're after.
This gives the number of times the midnight boundary is crossed between the two dates. You may decide to need to add one to this if you're including both dates in the count - or subtract one if you don't want to include either date.
Count work days between two dates
For workdays, Monday to Friday, you can do it with a single SELECT, like this:
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'
SELECT
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
If you want to include holidays, you have to work it out a bit...
How to find the number of days between two dates
You would use DATEDIFF
:
declare @start datetime
declare @end datetime
set @start = '2011-01-01'
set @end = '2012-01-01'
select DATEDIFF(d, @start, @end)
results = 365
so for your query:
SELECT dtCreated
, bActive
, dtLastPaymentAttempt
, dtLastUpdated
, dtLastVisit
, DATEDIFF(d, dtCreated, dtLastUpdated) as Difference
FROM Customers
WHERE (bActive = 'true')
AND (dtLastUpdated > CONVERT(DATETIME, '2012-01-0100:00:00', 102))
Get number of days between two dates in SQL
Use a combination of datediff
and lead
like this
datediff(Date_added,lead(Date_added,1))
Days between two dates In SQL Server
Try This:
SELECT ( DATEDIFF(DAY, '2016-11-12', '2016-11-30') ) / 365.00
How to calculate days count between 2 date in sql server
You can use datediff()
-- after testing for the limits of the period that you want:
select t.*,
datediff(day,
case when startdate < '2020-02-08' then '2020-02-08' else startdate end),
case when enddate > '2020-02-12' then '2020-02-12' else startdate end)
) + 1
from t;
The + 1
is because you are including the last day in the calculation.
Note: This only works correctly when there is an overlap. You want a filter:
(enddate >= '2020-02-08' or startdate <= '2020-02-12')
Based on the question, I'm not sure if this should be in a WHERE
clause or a CASE
expression. That is, do you want to filter out non-overlaps or do you want them to appear as 0
/NULL
?
Determine length of days between two dates and display as number in SQL Server
Try adding the following to the select clause ... DATEDIFF(DAY, ShippedDate, Orders.OrderDate)
AS days
Getting number of days for a specific month and year between two dates SQL
The generic formula for the number of overlapping days in two ranges is
MAX(MIN(end1, end2) - MAX(start1, start2) + 1, 0)
In your case you have one set of Start and End dates, you must construct the other from the given month and year using datefromparts
and eomonth
.
Unfortunately SQL Server doesn't support LEAST
and GREATEST
formulas as do MySQL and Oracle, so this is a bit painful to implement. Here's an example using variables:
declare @month int;
declare @year int;
declare @startDate date;
declare @endDate date;
declare @startOfMonth date;
declare @endOfMonth date;
declare @minEnd date;
declare @maxStart date;
set @month = 1;
set @year = 2020;
set @startDate = '2019-11-12';
set @endDate = '2020-01-13';
set @startOfMonth = datefromparts(@year, @month, 1)
set @endOfMonth = eomonth(@startOfMonth)
set @minEnd = case when @endDate < @endOfMonth then @endDate
else @endOfMonth
end;
set @maxStart = case when @startDate < @startOfMonth then @startOfMonth
else @startDate
end;
select case when datediff(day, @maxStart, @minEnd) + 1 < 0 then 0
else datediff(day, @maxStart, @minEnd) + 1
end as days_in_month
Output:
13
Demo on dbfiddle; this includes other sample date ranges.
You could implement something similar using a series of CTEs if the values are derived from a table.
Related Topics
In SQL, Is Update Always Faster Than Delete+Insert
Anonymous Table or Varray Type in Oracle
The Used Select Statements Have a Different Number of Columns (Redux!!)
Calculate the Number of Records for Each Date Between 2 Dates
How to Cast a String to Integer and Have 0 in Case of Error in the Cast with Postgresql
Must Declare the Scalar Variable
Match Only Entire Words with Like
Oracle Insert If Row Does Not Exist
SQL to Add Column with Default Value - Access 2003
How to Exclude Rows That Don't Join with Another Table
Sqlite Alter Table Add Multiple Columns in a Single Statement
Name Database Design Notation You Prefer and Why
Use Select Inside an Update Query
Autoincrement in Oracle to Already Created Table
How to Enable Ad Hoc Distributed Queries
How to Execute an in Lookup in SQL Using Golang