Date question, Can I get Q1, of 2006 begin and end dates?

I would like to get the first and last day of a quarter by passing in todays
date.
I can get the quarter were in with
select (DATEPART(Quarter,GetDate()))
Now I want to get the day the quarter started (Min day of the quarter) and
the Last day of the quarter (Max day of the quarter) i.e. 4/1/06 and 6/30/06
.
Tall MikeUse a calendar table.
http://www.aspfaq.com/2519
"TallMike" <TallMike@.discussions.microsoft.com> wrote in message
news:62668B64-9559-46FA-98CB-03E781C4B55F@.microsoft.com...
>I would like to get the first and last day of a quarter by passing in
>todays
> date.
> I can get the quarter were in with
> select (DATEPART(Quarter,GetDate()))
> Now I want to get the day the quarter started (Min day of the quarter)
> and
> the Last day of the quarter (Max day of the quarter) i.e. 4/1/06 and
> 6/30/06.
>
> --
> Tall Mike|||Either of these sring expressions can easilty be converted to a
datetime. I figured that they would be more easily understood without
wrapping them in that.
select convert(char(4),datepart(year,getdate())
) +
CASE DATEPART(Quarter,GetDate())
WHEN 1 THEN '0101'
WHEN 2 THEN '0401'
WHEN 3 THEN '0701'
WHEN 4 THEN '1001'
END as QuarterStartString,
convert(char(4),datepart(year,getdate())
) +
CASE DATEPART(Quarter,GetDate())
WHEN 1 THEN '0331'
WHEN 2 THEN '0630'
WHEN 3 THEN '0930'
WHEN 4 THEN '1231'
END as QuarterEndString
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 13:36:02 -0700, TallMike
<TallMike@.discussions.microsoft.com> wrote:
>I would like to get the first and last day of a quarter by passing in today
s
>date.
>I can get the quarter were in with
>select (DATEPART(Quarter,GetDate()))
>Now I want to get the day the quarter started (Min day of the quarter) and
>the Last day of the quarter (Max day of the quarter) i.e. 4/1/06 and 6/30/06.[/colo
r]|||Check this out, it works
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) as 'Last Q ended'
select DATEADD(qq, DATEDIFF(qq,0,getdate())-1, 0) as 'Last Q started'
Tall Mike
"Roy Harvey" wrote:

> Either of these sring expressions can easilty be converted to a
> datetime. I figured that they would be more easily understood without
> wrapping them in that.
> select convert(char(4),datepart(year,getdate())
) +
> CASE DATEPART(Quarter,GetDate())
> WHEN 1 THEN '0101'
> WHEN 2 THEN '0401'
> WHEN 3 THEN '0701'
> WHEN 4 THEN '1001'
> END as QuarterStartString,
> convert(char(4),datepart(year,getdate())
) +
> CASE DATEPART(Quarter,GetDate())
> WHEN 1 THEN '0331'
> WHEN 2 THEN '0630'
> WHEN 3 THEN '0930'
> WHEN 4 THEN '1231'
> END as QuarterEndString
> Roy Harvey
> Beacon Falls, CT
> On Wed, 3 May 2006 13:36:02 -0700, TallMike
> <TallMike@.discussions.microsoft.com> wrote:
>
>|||On Wed, 3 May 2006 15:25:02 -0700, TallMike
<TallMike@.discussions.microsoft.com> wrote:

>Check this out, it works
>select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) as 'Last Q ended'
>select DATEADD(qq, DATEDIFF(qq,0,getdate())-1, 0) as 'Last Q started'
Very nice! It came pretty close, but the Last Q ended was April 1,
not March 31.
But it didn't need much work.
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)-1 as 'Last Q ended'
select DATEADD(qq, DATEDIFF(qq,0,getdate())-1, 0) as 'Last Q started'
Roy Harvey
Beacon Falls, CT

date parameters

I need a parameter with the following options:
current week
last week
current month
current quarter
I have a dataset with today, monday of current week, current quarter number
and current month number, but i'm at a loss as to how to tie this in with my
report and how to put it into a parameter...any suggestions or referrals
would be most appreciated.Hi,
It is possible to ty values to a labels for a parameter through a query. All
you have to do is to create the dataset with two columns. One with you're
desired options and the other column with the corresponding number. In this
way it is possible to show the user the list of the four option, but use in
your report your own variables.
Jan Pieter Posthuma
"jmann" wrote:
> I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter number
> and current month number, but i'm at a loss as to how to tie this in with my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.|||This logic probably isn't right for your data but the basic idea should be
the same. For simplicity, I'm figuring that current week/month/quarter means
one week/month/quarter ago through today. You'll likely need to figure out
when each period begins using your data and make adjustments.
SELECT * FROM YourTables
WHERE OrderDate BETWEEN
CASE
WHEN @.Period = 'Current Week' THEN DATEADD(Week, -1, GETDATE())
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -2, GETDATE())
WHEN @.Period = 'Current Month' THEN CAST(CAST(MONTH(GETDATE()) AS
VarChar(2)) + '-1-' + CAST(YEAR(GETDATE()) AS VarChar(4)) AS DateTime)
WHEN @.Period = 'Current Quarter' THEN DATEADD(Quarter, -1, GETDATE())
END
AND
CASE
WHEN @.Period = 'Last Week' THEN DATEADD(Week, -1, GETDATE())
ELSE GETDATE()
END
Paul Turley
"jmann" <jmann@.discussions.microsoft.com> wrote in message
news:E474B451-652E-405F-B698-D0D1642D907D@.microsoft.com...
>I need a parameter with the following options:
> current week
> last week
> current month
> current quarter
> I have a dataset with today, monday of current week, current quarter
> number
> and current month number, but i'm at a loss as to how to tie this in with
> my
> report and how to put it into a parameter...any suggestions or referrals
> would be most appreciated.sql

date business intelligence

Hi,

I have a date dimension with the following attributes:

datekey

fulldate

weeknumber

month

quarter

semester

year

I have also created Named Calculation for Quarter, Semester and Month to make them more descriptive and associated them with the name column of each attribute.

e.g. Month: January 2005

Quarter: Q1 2005

However, I notice that the dates that were displayed are not correct. Sometimes I would have January 2017 even the records doesn't show any year 2017.

Why is this so? and how can i resolve this?

cherriesh

Take a look at the relationships between your attributes in the Date dimension. This may be the source of your problem.

To illustrate, consider the two month-related attributes in your dimension. You have both Month of Year, e.g. January, and Month, e.g. January 2007, attribute hierarchies. The Month Of Year attribute hierarchy is related to date but itself has no other relations. The Month attribute hierarchy is also related to date and has a relation to Quarter, e.g. Q1 2007, which has a relation to Year.

B.

|||

hi,

sorry but how do i do the checking? i went to Dimension Structure of my Date Dimension and in Quarter attribute (this has the named calculation 'QuarterDesc' on the Name Column) I dropped the Year attribute on the <New Attribute Relationship> and reprocess the cube. It's still the same.

anything i missed?

thanks a lot.

cherriesh

|||

No problem. For a first step, let's verify the TYPE property of each of your attribute hierarchies. Right-click each of the hierarchies, select Properties, and record the TYPE property value. Here are the attribute hierarchies you identified:

datekey

fulldate

weeknumber

month

quarter

semester

year

Once we have that info, we'll look at the relationships.

Thanks,
Bryan

|||

thanks a lot!

cherriesh