163

I'm trying to get the total number of days between two days:

1/1/2011
3/1/2011

RETURN
62

Is it possible to do in SQL Server?

shA.t
  • 16,580
  • 5
  • 54
  • 111
Pod Mays
  • 2,563
  • 7
  • 31
  • 44
  • 3
    Number of days *between* 1/1 and 3/1 would **never** return 62 in our current calendar. It would either return 58 or 59. Even if you include the start day and end day, that would be 60 or 61. – TylerH Sep 18 '17 at 15:09
  • 14
    And if you're not in the USA, it will return 2 days. – MGOwen Feb 12 '18 at 01:00

9 Answers9

311

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.

Will A
  • 24,780
  • 5
  • 50
  • 61
41

SQL Server DateDiff

DECLARE @startdate datetime2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate datetime2 = '2009-05-04 12:10:09.3312722'; 
SELECT DATEDIFF(day, @startdate, @enddate);
Khepri
  • 9,547
  • 5
  • 45
  • 61
18

You can try this MSDN link

DATEDIFF ( datepart , startdate , enddate )
SELECT DATEDIFF(DAY, '1/1/2011', '3/1/2011')
Amit
  • 21,570
  • 27
  • 74
  • 94
17

See DateDiff:

DECLARE @startdate date = '2011/1/1'
DECLARE @enddate date = '2011/3/1'
SELECT DATEDIFF(day, @startdate, @enddate)
Kols
  • 3,641
  • 2
  • 34
  • 42
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
14

Another date format

select datediff(day,'20110101','20110301')
shA.t
  • 16,580
  • 5
  • 54
  • 111
cakiran
  • 341
  • 2
  • 6
5
SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
shA.t
  • 16,580
  • 5
  • 54
  • 111
VMAtm
  • 27,943
  • 17
  • 79
  • 125
2

This is working for me - 

SELECT DATEDIFF(DAY, startdate, enddate) AS DayCount

Example : SELECT DATEDIFF(DAY, '11/30/2019', GETDATE()) AS DayCount
bulbul bd
  • 166
  • 1
  • 1
  • 9
-1

if you want to do same thing Store Procedure then you need to apply below code.

select  (datediff(dd,'+CHAR(39)+ convert(varchar(10),@FromDate  ,101)+ 
 CHAR(39)+','+CHAR(39)+ convert(varchar(10),@ToDate  ,101) + CHAR(39) +')) 
 Daysdiff

where @fromdate and @todate is Parameter of the SP

Bha15
  • 231
  • 3
  • 8
-1
DECLARE @FDate DATETIME='05-05-2019' /*This is first date*/
 GETDATE()/*This is Current date*/
SELECT (DATEDIFF(DAY,(@LastDate),GETDATE())) As DifferenceDays/*this query will return no of days between firstdate & Current date*/
Code
  • 679
  • 5
  • 9