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.
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 |
|
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 MVPhttp://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 suggestCan you tell what exactly you're trying to get as output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 DATETIMEDECLARE @DateTo DATETIMESET @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 NumberOfDaysFROM dbo.Table1WHERE ReqDate BETWEEN @DateFrom AND @DateToGROUP 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|