SQL Server: Get Total Days Between Two Dates

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



Leave a reply



Submit