Board index MS SQL Server

how to set first day of week in a dimention

how to set first day of week in a dimention

Post by wenki » Tue, 23 Apr 2002 19:24:16



Dear all,

i would like to cread a date dimention, with 3 level, i.e
year, week, and day. By default the day is from Sunday,
Monday...to Saturday. Is there any way to set the week
day start from Monday to Sunday?

Thanks and regards,
Wenkie

 
 
 
Top

how to set first day of week in a dimention

Post by Andrew Mc Va » Tue, 23 Apr 2002 21:29:08


Straight out of BOL for DATEPART

"The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January
1 of any year defines the starting number for the week datepart, for
example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
The weekday (dw) datepart returns a number that corresponds to the day of
the week, for example: Sunday = 1, Saturday = 7. The number produced by the
weekday datepart depends on the value set by SET DATEFIRST, which sets the
first day of the week."

When you use the TIME dimension wizard in AS you can set the first day of
the year which in turn sets your first day of week.

~Andrew.


Quote:> Dear all,

> i would like to cread a date dimention, with 3 level, i.e
> year, week, and day. By default the day is from Sunday,
> Monday...to Saturday. Is there any way to set the week
> day start from Monday to Sunday?

> Thanks and regards,
> Wenkie


 
 
 
Top

how to set first day of week in a dimention

Post by wenki » Wed, 24 Apr 2002 15:06:44


Thanks Andrew,

I have try to create a time dimension, and there are only
allow to set the first day of year, is there other way to
set the first week?

IF i use Set DateFirst, so, where should i put the
command? Under the level property? The dimension have 3
level, year, week, and day.

Thanks and regards,
Wenkie

Quote:>-----Original Message-----
>Straight out of BOL for DATEPART

>"The week (wk, ww) datepart reflects changes made to SET
DATEFIRST. January
>1 of any year defines the starting number for the week
datepart, for
>example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is
any year.
>The weekday (dw) datepart returns a number that

corresponds to the day of
Quote:>the week, for example: Sunday = 1, Saturday = 7. The

number produced by the
Quote:>weekday datepart depends on the value set by SET

DATEFIRST, which sets the
>first day of the week."

>When you use the TIME dimension wizard in AS you can set
the first day of
>the year which in turn sets your first day of week.

>~Andrew.



>> Dear all,

>> i would like to cread a date dimention, with 3 level,
i.e
>> year, week, and day. By default the day is from Sunday,
>> Monday...to Saturday. Is there any way to set the week
>> day start from Monday to Sunday?

>> Thanks and regards,
>> Wenkie

>.

 
 
 
Top

how to set first day of week in a dimention

Post by Andrew Mc Va » Wed, 24 Apr 2002 19:42:07


I do not like using the TIME wizard so I create my own dimension using a
VIEW. This example sets the first day of the week to a Tuesday. The key
values are used to set the ORDER BY properties for the dimension.

----------------------------------------------------------------------------
---------------------------
SET DATEFIRST 2
GO
ALTER    VIEW dTIME as

SELECT DISTINCT
inception_date,
convert(varchar,datepart(yy,inception_date)) as [Year],
'Q'+convert(varchar,datepart(qq,inception_date))+'/'+ convert(varchar,
datepart(yy,inception_date)) as Quart,
datepart(qq,inception_date) as QuartKey,
DateName(m,inception_date)+'/'+ convert(varchar,
datepart(yy,inception_date)) as [Month],
datepart(m,inception_date) as MonthKey,
'Week '+ convert(varchar,DatePart(ww,inception_date)) +' /'+
convert(varchar, datepart(yy,inception_date)) as [Week],
datepart(ww,inception_date) as WeekKey,

convert(varchar, datepart(dd,inception_date))+'/'+
LEFT(DateName(m,inception_date),3)+'/'+convert(varchar,
datepart(yy,inception_date)) as [Day],
convert(char,inception_date, 112) as DayKey
FROM Base
----------------------------------------------------------------------------
-----------------------------------

~Andrew.


> Thanks Andrew,

> I have try to create a time dimension, and there are only
> allow to set the first day of year, is there other way to
> set the first week?

> IF i use Set DateFirst, so, where should i put the
> command? Under the level property? The dimension have 3
> level, year, week, and day.

> Thanks and regards,
> Wenkie

> >-----Original Message-----
> >Straight out of BOL for DATEPART

> >"The week (wk, ww) datepart reflects changes made to SET
> DATEFIRST. January
> >1 of any year defines the starting number for the week
> datepart, for
> >example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is
> any year.
> >The weekday (dw) datepart returns a number that
> corresponds to the day of
> >the week, for example: Sunday = 1, Saturday = 7. The
> number produced by the
> >weekday datepart depends on the value set by SET
> DATEFIRST, which sets the
> >first day of the week."

> >When you use the TIME dimension wizard in AS you can set
> the first day of
> >the year which in turn sets your first day of week.

> >~Andrew.



> >> Dear all,

> >> i would like to cread a date dimention, with 3 level,
> i.e
> >> year, week, and day. By default the day is from Sunday,
> >> Monday...to Saturday. Is there any way to set the week
> >> day start from Monday to Sunday?

> >> Thanks and regards,
> >> Wenkie

> >.

 
 
 
Top

how to set first day of week in a dimention

Post by Wenki » Fri, 03 May 2002 14:49:54


Thanks ^_^
>-----Original Message-----
>I do not like using the TIME wizard so I create my own
dimension using a
>VIEW. This example sets the first day of the week to a
Tuesday. The key
>values are used to set the ORDER BY properties for the
dimension.

>---------------------------------------------------------
-------------------
>---------------------------
>SET DATEFIRST 2
>GO
>ALTER    VIEW dTIME as

>SELECT DISTINCT
>inception_date,
>convert(varchar,datepart(yy,inception_date)) as [Year],
>'Q'+convert(varchar,datepart(qq,inception_date))+'/'+
convert(varchar,
>datepart(yy,inception_date)) as Quart,
>datepart(qq,inception_date) as QuartKey,
>DateName(m,inception_date)+'/'+ convert(varchar,
>datepart(yy,inception_date)) as [Month],
>datepart(m,inception_date) as MonthKey,
>'Week '+ convert(varchar,DatePart(ww,inception_date))
+' /'+
>convert(varchar, datepart(yy,inception_date)) as [Week],
>datepart(ww,inception_date) as WeekKey,

>convert(varchar, datepart(dd,inception_date))+'/'+
>LEFT(DateName(m,inception_date),3)+'/'+convert(varchar,
>datepart(yy,inception_date)) as [Day],
>convert(char,inception_date, 112) as DayKey
>FROM Base
>---------------------------------------------------------
-------------------
>-----------------------------------

>~Andrew.





- Show quoted text -

>> Thanks Andrew,

>> I have try to create a time dimension, and there are
only
>> allow to set the first day of year, is there other way
to
>> set the first week?

>> IF i use Set DateFirst, so, where should i put the
>> command? Under the level property? The dimension have 3
>> level, year, week, and day.

>> Thanks and regards,
>> Wenkie

>> >-----Original Message-----
>> >Straight out of BOL for DATEPART

>> >"The week (wk, ww) datepart reflects changes made to
SET
>> DATEFIRST. January
>> >1 of any year defines the starting number for the week
>> datepart, for
>> >example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx
is
>> any year.
>> >The weekday (dw) datepart returns a number that
>> corresponds to the day of
>> >the week, for example: Sunday = 1, Saturday = 7. The
>> number produced by the
>> >weekday datepart depends on the value set by SET
>> DATEFIRST, which sets the
>> >first day of the week."

>> >When you use the TIME dimension wizard in AS you can
set
>> the first day of
>> >the year which in turn sets your first day of week.

>> >~Andrew.



>> >> Dear all,

>> >> i would like to cread a date dimention, with 3
level,
>> i.e
>> >> year, week, and day. By default the day is from
Sunday,
>> >> Monday...to Saturday. Is there any way to set the
week
>> >> day start from Monday to Sunday?

>> >> Thanks and regards,
>> >> Wenkie

>> >.

>.

 
 
 
Top

how to set first day of week in a dimention

Post by Wenki » Fri, 03 May 2002 14:49:47


Thanks ^_^
>-----Original Message-----
>I do not like using the TIME wizard so I create my own
dimension using a
>VIEW. This example sets the first day of the week to a
Tuesday. The key
>values are used to set the ORDER BY properties for the
dimension.

>---------------------------------------------------------
-------------------
>---------------------------
>SET DATEFIRST 2
>GO
>ALTER    VIEW dTIME as

>SELECT DISTINCT
>inception_date,
>convert(varchar,datepart(yy,inception_date)) as [Year],
>'Q'+convert(varchar,datepart(qq,inception_date))+'/'+
convert(varchar,
>datepart(yy,inception_date)) as Quart,
>datepart(qq,inception_date) as QuartKey,
>DateName(m,inception_date)+'/'+ convert(varchar,
>datepart(yy,inception_date)) as [Month],
>datepart(m,inception_date) as MonthKey,
>'Week '+ convert(varchar,DatePart(ww,inception_date))
+' /'+
>convert(varchar, datepart(yy,inception_date)) as [Week],
>datepart(ww,inception_date) as WeekKey,

>convert(varchar, datepart(dd,inception_date))+'/'+
>LEFT(DateName(m,inception_date),3)+'/'+convert(varchar,
>datepart(yy,inception_date)) as [Day],
>convert(char,inception_date, 112) as DayKey
>FROM Base
>---------------------------------------------------------
-------------------
>-----------------------------------

>~Andrew.





- Show quoted text -

>> Thanks Andrew,

>> I have try to create a time dimension, and there are
only
>> allow to set the first day of year, is there other way
to
>> set the first week?

>> IF i use Set DateFirst, so, where should i put the
>> command? Under the level property? The dimension have 3
>> level, year, week, and day.

>> Thanks and regards,
>> Wenkie

>> >-----Original Message-----
>> >Straight out of BOL for DATEPART

>> >"The week (wk, ww) datepart reflects changes made to
SET
>> DATEFIRST. January
>> >1 of any year defines the starting number for the week
>> datepart, for
>> >example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx
is
>> any year.
>> >The weekday (dw) datepart returns a number that
>> corresponds to the day of
>> >the week, for example: Sunday = 1, Saturday = 7. The
>> number produced by the
>> >weekday datepart depends on the value set by SET
>> DATEFIRST, which sets the
>> >first day of the week."

>> >When you use the TIME dimension wizard in AS you can
set
>> the first day of
>> >the year which in turn sets your first day of week.

>> >~Andrew.



>> >> Dear all,

>> >> i would like to cread a date dimention, with 3
level,
>> i.e
>> >> year, week, and day. By default the day is from
Sunday,
>> >> Monday...to Saturday. Is there any way to set the
week
>> >> day start from Monday to Sunday?

>> >> Thanks and regards,
>> >> Wenkie

>> >.

>.

 
 
 
Top

1. HELP: First Day in Week Setting and First Week in Year Help

1.
I would like to change the SQL-Server 7 configuration for
- the first day of week to Monday and NOT Sunday AND
- that the first week in a year is the first full week

I know the configuration option "SET DATEFIRST", but this is only for a
session.

I would like to change these settings ONCE for the whole SQL Server.

2.
Found something interesting:
If I want to fill a table with all weeks from 1997 to 2005 and a unique ID,
sometimes,
weeks are missing (2000-01) for example... This problem starts with 2000...
Y2K Problem?
But this problem only happens, if I set the first day of week to Sunday, if
it is set to Monday, it works!

I am confused... PLEASE HELP!

Here is the code for it:
CREATE TABLE [dbo].[DBL_Dates] (
 [Number] [T_PCID] NOT NULL,
 [Week] VARCHAR(6) NOT NULL
 CONSTRAINT [PK_DBL_Dates] PRIMARY KEY ( [Number] )
) ON [PRIMARY]






SET DATEFIRST 7 -- 7 does NOT work, 1 works





BEGIN

 BEGIN




 END



also... thats why I tried with DAY
END

2. Deleting files in ObjectPal in Win 95

3. set the first day of the week

4. Cube Deployment

5. Set first day of week

6. Stored Procedure to kill users sessions

7. How to set up the first week day for the DateDiff

8. termcap info needed

9. set first day of week

10. Week : howto find the date of the first day of a week#

11. Query to return the first day of the current week

12. PL SQL - how to get the first day of the week

13. Weeks, first day on Monday


All times are UTC
Board index