Number of days in previous quarter?

Hello
I need to calculate the number of days in the previous quarter relative to
'today'.
I thought it would be as simple as the number of days between today and one
quarter earlier:
datediff(dd,dateadd(qq,-1,getdate()),getdate())
but that gives 92 (it should be 31+28+31=90 today).
Subtracting months gives the same result:
select datediff(dd,dateadd(mm,-3,getdate()),getdate())
What am I thinking wrong here,
and what would be the correct formula?
I have tried several things, but it's turning into a morass of datepart(),
dateadd(), iif(), cast() etc
TIA
JanSurely if you're running it today (June 9th), then it will give you the
number of days since March 9th, which is 92. (31 days in March and May, 30
in April).
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message
news:O22KBI5iGHA.4284@.TK2MSFTNGP05.phx.gbl...
> Hello
> I need to calculate the number of days in the previous quarter relative to
> 'today'.
> I thought it would be as simple as the number of days between today and
> one
> quarter earlier:
> datediff(dd,dateadd(qq,-1,getdate()),getdate())
> but that gives 92 (it should be 31+28+31=90 today).
> Subtracting months gives the same result:
> select datediff(dd,dateadd(mm,-3,getdate()),getdate())
> What am I thinking wrong here,
> and what would be the correct formula?
> I have tried several things, but it's turning into a morass of datepart(),
> dateadd(), iif(), cast() etc
> TIA
> Jan
>|||Aargh, yes of course! It was late last evening.
Now the 'only' thing left is finding the right solution ;-)
Jan
"Rob Farley" <rob_farley@.hotmail.com> wrote in message
news:eVx2jl5iGHA.4080@.TK2MSFTNGP03.phx.gbl...
> Surely if you're running it today (June 9th), then it will give you the
> number of days since March 9th, which is 92. (31 days in March and May, 30
> in April).
> "Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message
> news:O22KBI5iGHA.4284@.TK2MSFTNGP05.phx.gbl...
to
datepart(),
>|||How about :
select datediff(dd,LastQuarter,ThisQuarter)
from
(
select
dateadd(qq,datediff(qq,0,current_timesta
mp),0) as ThisQuarter,
dateadd(qq,datediff(qq,0,current_timesta
mp)-1,0) as LastQuarter
) as a
ThisQuarter & LastQuarter represent the first day of the current quarter
and the first day of the previous quarter respectively (using a trick
about stripping off parts of a datetime value - usually used to strip
off the time part by specifying dateadd(dd,...)). So the number of days
in the previous quarter, relative to today, is just the difference in
days between ThisQuarter and LastQuarter (as defined above).
*mike hodgson*
http://sqlnerd.blogspot.com
Jan Doggen wrote:

>Aargh, yes of course! It was late last evening.
>Now the 'only' thing left is finding the right solution ;-)
>Jan
>"Rob Farley" <rob_farley@.hotmail.com> wrote in message
>news:eVx2jl5iGHA.4080@.TK2MSFTNGP03.phx.gbl...
>
>to
>
>datepart(),
>
>
>|||Smart!
I never thought about selecting from a select.
Must be a blind spot because I was only dealing with variables and not
tabular data ;-)
I was already working towards a table 'DaysInQuarter' so that I could just
look it up:
CREATE TABLE DaysInQuarter
(Yr INT, Q Int, YrQ CHAR(6), DIQ INT)
INSERT INTO DaysInQuarter VALUES(2000,1,'200001',91)
INSERT INTO DaysInQuarter VALUES(2000,2,'200002',91)
INSERT INTO DaysInQuarter VALUES(2000,3,'200003',92)
INSERT INTO DaysInQuarter VALUES(2000,4,'200004',92)
etc
then:
select DIQ from DaysInQuarter
WHERE Q = datepart(qq, dateadd(mm,-3,getdate()))
AND Yr = year(dateadd(mm,-3,getdate()))
(kind of 'brute force' since Q2, Q3 and Q4 never change)
Thanks
Jan|||You could have done it with a calendar table, that's not a bad idea -
auxiliary tables (like calendar & numbers tables (see
http://www.aspfaq.com/2519)) come in handy for solving all sorts of
problems. As for my solution, there's no reason I needed to use a
derived table like I did, it just made the example clearer and made it
easier to explain. I could have included the whole lot in a single
select (with no FROM clause) but it would have made it a bit uglier and
more confusing to try to understand.
*mike hodgson*
http://sqlnerd.blogspot.com
Jan Doggen wrote:

>Smart!
>I never thought about selecting from a select.
>Must be a blind spot because I was only dealing with variables and not
>tabular data ;-)
>I was already working towards a table 'DaysInQuarter' so that I could just
>look it up:
>CREATE TABLE DaysInQuarter
>(Yr INT, Q Int, YrQ CHAR(6), DIQ INT)
>INSERT INTO DaysInQuarter VALUES(2000,1,'200001',91)
>INSERT INTO DaysInQuarter VALUES(2000,2,'200002',91)
>INSERT INTO DaysInQuarter VALUES(2000,3,'200003',92)
>INSERT INTO DaysInQuarter VALUES(2000,4,'200004',92)
>etc
>then:
>select DIQ from DaysInQuarter
>WHERE Q = datepart(qq, dateadd(mm,-3,getdate()))
>AND Yr = year(dateadd(mm,-3,getdate()))
>(kind of 'brute force' since Q2, Q3 and Q4 never change)
>Thanks
>Jan
>
>