## how to set first day of week in a dimention

### how to set first day of week in a dimention

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

### how to set first day of week in a dimention

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

### how to set first day of week in a dimention

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

>.

### how to set first day of week in a dimention

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

> >.

### how to set first day of week in a dimention

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

>> >.

>.

### how to set first day of week in a dimention

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

>> >.

>.

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!

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