count days between two dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

count days between two dates

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2012-09-28 : 12:53:29

Hi All.

I have table where I keep request and date of request. And I would like to calculate days between to two date. How to calculate number of days between to date? In the result I would like to have number of request days in Month, in Quarter, and in Year.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-28 : 12:58:23

You can use the DATEDIFF function for this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 13:33:52

you mean as x quarters y months z days format?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

eugz
Posting Yak Master

210 Posts

Posted - 2012-09-28 : 14:17:00

Hi thanks for replay.

I created select


	declare @DateFrom datetime
	declare @DateTo datetime
	set @DateFrom = '2011/3/07'
	set @DateTo = '2012/08/09'
	
	SELECT 
	dateadd(year, datediff(year, 0, ReqDate), 0) as call_date
	,DATEDIFF(day, ReqDate, GETDATE()) AS NumberOfDays
    from   dbo.Table1
	where convert(varchar(10),ReqDate,111) between convert(varchar(10),@DateFrom,111) and convert(varchar(10),@DateTo,111)
    group by dateadd(year, datediff(year, 0, ReqDate), 0),ReqDate

But it doesn't work. What is problem? How it fix?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 14:23:43

quote:

Originally posted by eugz

Hi thanks for replay.

I created select


	declare @DateFrom datetime
	declare @DateTo datetime
	set @DateFrom = '2011/3/07'
	set @DateTo = '2012/08/09'
	
	SELECT 
	dateadd(year, datediff(year, 0, ReqDate), 0) as call_date
	,DATEDIFF(day, ReqDate, GETDATE()) AS NumberOfDays
    from   dbo.Table1
	where convert(varchar(10),ReqDate,111) between convert(varchar(10),@DateFrom,111) and convert(varchar(10),@DateTo,111)
    group by dateadd(year, datediff(year, 0, ReqDate), 0),ReqDate

But it doesn't work. What is problem? How it fix?

Thanks.

without understanding what output you're expecting its hard to suggest

Can you tell what exactly you're trying to get as output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-28 : 14:26:11

I would think you need something like this, assuming you are trying to get the total days

DECLARE @DateFrom DATETIME
DECLARE @DateTo DATETIME
SET @DateFrom = '2011/3/07'
SET @DateTo = '2012/08/09'

SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, ReqDate), 0) AS call_date,
       SUM(DATEDIFF(DAY, ReqDate, GETDATE())) AS NumberOfDays
FROM   dbo.Table1
WHERE  ReqDate BETWEEN @DateFrom AND @DateTo
GROUP BY
       DATEADD(YEAR, DATEDIFF(YEAR, 0, ReqDate), 0)

eugz
Posting Yak Master

210 Posts

Posted - 2012-09-28 : 14:29:58

In column call_date I would like to have Years and NumberOfDays number of day when made request between DateFrom and DateTo for that year.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-28 : 15:54:37

quote:
Originally posted by eugz

In column call_date I would like to have Years and NumberOfDays number of day when made request between DateFrom and DateTo for that year.

Thanks.

are datefrom dateto table fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources