Hi everybody,

I am trying to build a custom time dimension, and I am having problems
with the month level

My field within the database table has the following data:

"mis_dimension_process_date"."process_date" = "2005011X"

Where:

"2005" = year
"01" = month
"1X" = period (sub month level)

In the dimension definition, I am using a year level, quarter level,
month level and period level. The year, quarter and month levels are
based on the standard definition used by AS with any Time dimension,
all settings are exactly as the standard definition

But instead of showing the month name (i.e. January, Febraury, ...) the
month level is shown as a numeric value (i.e. 1, 2, ...)

I have the following definition for the month level:

Key Column: DatePart(month, '01' + '/' +
substring("dbo"."mis_dimension_process_date"."proc ess_date", 5, 2) +
'/' + substring("dbo"."mis_dimension_process_date"."proc ess_date", 1,
4))

Name Column: convert(CHAR, DatePart(month, '01' + '/' +
substring("dbo"."mis_dimension_process_date"."proc ess_date", 5, 2) +
'/' + substring("dbo"."mis_dimension_process_date"."proc ess_date", 1,
4)))

So I am just creating a standard date value based on the data I have in
the database by adding a day component. It works fine with the year and
quarter levels

Any ideas ?

MSSQL 2000 Enterprise Edition + SP3

Thanks in advance,
Pedro

In article <1128336951.842866.100600 (AT) g47g2000cwa (DOT) googlegroups.com>,
pedrojmendoza (AT) gmail (DOT) com says...
The trick is to use the DATENAME() function instead of using DATEPART()
and CONVERT(), (which would just give you a string of '1'.)

Name Column: DateName(month, '01' + '/' +
substring("dbo"."mis_dimension_process_date"."proc ess_date", 5, 2) +
'/' + substring("dbo"."mis_dimension_process_date"."proc ess_date", 1,
4))


--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Thank you very much Darren !!!