BUG: Date Formats Change to DBTimeStamp After Server Processes Cube (319636)


The information in this article applies to:

  • Microsoft SQL Server 2000 Analysis Services
This article was previously published under Q319636
BUG #: 12708 (plato7x)

SYMPTOMS

If the Member Name column and the Member Key column of a dimension level come from the same source column, and the source column data type is datetime, the format of that level always changes to DBTimeStamp after the server processes a cube that uses the dimension.

WORKAROUND

To work around this behavior, change the Member Name Column of the level to include whatever format you want.

For example, what if the dimension table is in SQL Server? Originally, the Member Name column and the Member Key column are defined as time_by_day.the_date. To work around this problem, change the Member Name column of the level to: 
Convert(varchar, "time_by_day"."the_date", 101)

With this SQL specific syntax, the last parameter, 101, controls the format of the date. For information about other possible formats, see the "Convert" topic in SQL Server Books Online. Other data sources ought to support similar functionality. For example, Microsoft Access uses the Format function.

STATUS

Microsoft has confirmed that this is a problem in SQL Server 2000 Analysis Service.

MORE INFORMATION

Steps to Reproduce the Behavior

To reproduce the behavior, follow these steps:
  1. Open the FoodMart 2000 sample database.
  2. Edit the Time dimension.
  3. Add a Day level under the month, by using the time_by_day.the_date column.
  4. Save, and process the dimension.
  5. Browse the dimension to the day level. Note the format of the date values.
  6. Process the Sales cube.
  7. Browse the Time dimension to the lowest level. Note the format of the dates.
Modification Type:MajorLast Reviewed:6/25/2004
Keywords:kbbug kbpending KB319636
©2004 Microsoft Corporation. All rights reserved.