Century date conversions

Hi,
I'm trying to load date fields into SQLServer using DTS, but the
format of the raw data is the number of days since 1 Jan 1900. How do
I convert this to a useful format, is there a standard conversion
routine?

Thanks
Timtim.philbrook@.cazenove.com (Helsop) wrote in message news:<1609a822.0408180233.9fbed33@.posting.google.com>...
> Hi,
> I'm trying to load date fields into SQLServer using DTS, but the
> format of the raw data is the number of days since 1 Jan 1900. How do
> I convert this to a useful format, is there a standard conversion
> routine?
> Thanks
> Tim

There are (at least) two possible solutions. First, load the data into
a staging table and clean it up with TSQL, before an INSERT into the
final table:

insert into dbo.Destination
(col1, datetime_column, ...)
select col1, dateadd(dd, numdays_column, '19000101'), ...
from dbo.Staging

Alternatively, if you want to do the transformation in DTS, then you
could use the VBScript DateAdd() function in an ActiveX column
transformation to achieve the same thing.

Simon|||--> use DATEADD(dd,@.OffsetDays,'1 Jan 1900')

DECLARE @.OffsetDays BIGINT
SET @.OffSetDays = 38217
select DATEADD(dd,@.OffsetDays,'1 Jan 1900')

Tx

Cell Calculation for cells that already has values

Hi,

I have the table like this

Jan Feb .. Dec

account 50 20 ...

On the cube i would like to make it using calculated cells and that look like this

account 50 70 ...

Same cells has before.

I have tried a few stuff but i cant put values on top of the ones that are already there, so i get the values in the cell and not the values in the calculated cell.

Funny, though, i have put a constant (1) i the 'AS' part of the calculated cell syntax and it adds the cell value with the constant. But if i try to add with the value time.lag(1) SSAS just ignores the value.

Is there a way to do this by calculated cell?

Thank you

Have you considered simply creating a calculated measure using the YTD() or PeriodsToDate() functions? If you did not want the end users to see the underlying measure you could set it's visible property to false.|||

Hello,

The problem with calculated members is that the user would have to change the query when browsing on the cube.

What i would like is that, for the users, browsing on the this account or any other i could use the same measure. And for that reason i need it to be a calculated cell.

Is there a way to solve this?

|||

You did not mention which version of Analysis Services you are using. I am assuming from the SSAS acronym that you are using 2005, not 2000.

You would not have to change any queries if you renamed the current measure and then created a new calculated measure with the same name as the original. This would be my preferred approach as you could still get to both the cumulative and the incremental amounts.

However, you could replace the value of a measure using an MDX Script assignment.

I am not clear on exactly what your business requirements are, but the following is a sample bit of MDX Script which works against the Adventure Works database to simply adds the previous member to the current member for the [Sales Amount] measure.

([Measures].[Sales Amount]

,[Date].[Fiscal].Members) = ([Date].[Fiscal].Prevmember)

+ ([Date].[Fiscal].CurrentMember);

|||

You did not mention which version of Analysis Services you are using. I am assuming from the SSAS acronym that you are using 2005, not 2000.

You would not have to change any queries if you renamed the current measure and then created a new calculated measure with the same name as the original. This would be my preferred approach as you could still get to both the cumulative and the incremental amounts.

However, you could replace the value of a measure using an MDX Script assignment.

I am not clear on exactly what your business requirements are, but the following is a sample bit of MDX Script which works against the Adventure Works database to simply adds the previous member to the current member for the [Sales Amount] measure.

([Measures].[Sales Amount]

,[Date].[Fiscal].Members) = ([Date].[Fiscal].Prevmember)

+ ([Date].[Fiscal].CurrentMember);

|||

Hello,

This was a good solution, inline with the other solution in the conversation.

I think that i could try altough it will clash a bit with all my other calculated cell. I did not remember to rename the calculated member to the actual measer name.

With all this i assume that the cell calculation doesn't allow this task.

Thank you