Combining date and time

I have 2 fields, one containing just the date and another containing the time. I want to combine them into a proper date/time format. No matter what I try, I lose 2 days in the process. Combining

2005-12-21 00:00:00.000
and
1899-12-30 14:30:00.000

will get me the 19th at 14:30. What am I missing here?

TIAThis was addressed recently in the following thread: link (http://www.dbforums.com/t1203973.html)

Regards,

hmscott|||Thank you. I searched and somehow missed that thread. Bad choice of search words I suppose. I guess my problem was not using varchar. I had tried using convert but used datetime as the type, using a style of 101 for the date portion and 108 for the time. This seems to be working correctly:

Convert(datetime,Convert(varchar(11), DateField, 101) + ' ' + Convert(varchar(8), ReqTime, 108))

ColumnName Property on an Attribute

I have a Matter dimension with an OrgUnit attribute, the ColumnKey is Matter.OrgUnitKey and the ColumnName is OrgUnit.Name:

This is the select generated when processing the cube is:

SELECT DISTINCT[Foundation_Matter].[OrgUnit1Key] AS [Foundation_MatterOrgUnit1Key0_0],[Foundation_OrgUnit1].[Name] AS [Foundation_OrgUnit1Name1_0]

FROM [Foundation].[Matter] AS [Foundation_Matter],[Foundation].[OrgUnit1] AS [Foundation_OrgUnit1],[Foundation].[Person] AS [Foundation_Person]

WHERE (([Foundation_Person].[PrimaryOrgUnit1Key] = [Foundation_OrgUnit1].[Key])

AND([Foundation_Matter].[BillingPersonKey]= [Foundation_Person].[Key]))

Foundation_MatterOrgUnit1Key0_0

Foundation_OrgUnit1Name1_0

-1

(Unknown)

2

Firm 1

2

Firm 2

2

Firm 3

3

Firm 1

3

Firm 2

3

Firm 3

4

Firm 1

4

Firm 2

4

Firm 3

This is more or less the select I was expecting:

SELECT DISTINCT[Foundation_Matter].[OrgUnit1Key] AS [Foundation_MatterOrgUnit1Key0_0],[Foundation_OrgUnit1].[Name] AS [Foundation_OrgUnit1Name1_0]

FROM [Foundation].[Matter] AS [Foundation_Matter],[Foundation].[OrgUnit1] AS [Foundation_OrgUnit1]

WHERE ([Foundation_Matter].[OrgUnit1Key] = [Foundation_OrgUnit1].[Key])

Foundation_MatterOrgUnit1Key0_0

Foundation_OrgUnit1Name1_0

-1

(Unknown)

2

Firm 1

3

Firm 2

4

Firm 3

The question is how is the SQL statement build?

I have defined the following relationships:

Matter.OrgUnitKey = OrgUnit.Key

Matter.BillingPersonKey = Person.Key

Peson.OrgUnitKey = OrgUnit.Key

I can define a select in the DSV to get the Name with the Key but like to avoid it if I can,

Please Help

It looks like you have a loop in your relationships and SSAS has chosen to go through the person table to get to the OrgUnit. Do you have any attributes in your Matter dimension that reference the Person table? If not, you might need to create a named query in the DSV for the OrgUnit table so that there are two instances of it. In this way you could break the realtionship loop into 2 pieces so SSAS would not be able to choose the wrong path.

|||

There is a relationship Matter to Person and Person to OrgUnit.I was trying to avoid going to the DVS, but it seems unavoidable.There is more that one Dimension that will have this problem.

Thank you for you quick response.