DATEPART query

[ioscat]

Hello!
As help says, DATEPART returns an integer. But TI process sees it as float. How to solve this newbie trouble? I need to use items as elements of dimensions.

Безымянный2.png (26.18 KiB) Viewed 5141 times

[Steve Rowe]

If you can't set the items to text on the variables data tab then use NumberToString and SubSt or Delet and maybe Scan too to get to the bit of the string you need from the converted number.

Cheers,

[ioscat]

wowow! of course they set to string! another way i can't use them as elements of dimension in process!

If you can't set the items to text on the variables data tab then use NumberToString and SubSt or Delet and maybe Scan too to get to the bit of the string you need from the converted number.

Cheers,

in Russia we call this solution "The Crutch" cos it does not solve reason but mask it and cost lot of machine resources and may cause to reworking if some conditions change. I believe best solution exists, but i can't find it myself.

[tomok]

As help says, DATEPART returns an integer.

What help would that be? Not TM1, since that's a SQL function.

But TI process sees it as float.

That's because as far as TM1 is concerned there are only two data types, floating point numeric and text. The concept of an integer does not exist. If TI detects only numeric characters in a field of a data source, it is going to assume the data is numeric and therefore show it as floating. There is no way around this. You really have only two options; 1) convert the field to text using NumberToString (my recommendation) or 2) trick TI into thinking the data is string by concatenating a letter to the end of field (as part of your SQL statement) and then deleting the character in the TI by using the SUBST function. I don't know why anyone would want to try option 2, I just threw it out there for kicks. As to option 1, I use it all the time, don't see what the big deal is.

[ioscat]

What help would that be? Not TM1, since that's a SQL function.

you are right, i mean http://msdn.microsoft.com/en-us/library/ms174420.aspx

That's because as far as TM1 is concerned there are only two data types, floating point numeric and text. The concept of an integer does not exist. If TI detects only numeric characters in a field of a data source, it is going to assume the data is numeric and therefore show it as floating. There is no way around this. You really have only two options; 1) convert the field to text using NumberToString (my recommendation) or 2) trick TI into thinking the data is string by concatenating a letter to the end of field (as part of your SQL statement) and then deleting the character in the TI by using the SUBST function. I don't know why anyone would want to try option 2, I just threw it out there for kicks. As to option 1, I use it all the time, don't see what the big deal is.

=-(

[Steve Rowe]

Best guess would be to force the result to string in the SQL statement and then set the result to string in the variables tab.

This sort of feels most right in terms of process. This might not work since TI might still guess that they are numbers and convert them to floating point again, not sure.

It's open to debate if that's anymore efficient than converting the numeric to a string in the TI but I guess if you do the type conversion in the SQL then you shouldn't need to drop the ".00000"

You are going to need to do the type conversion somewhere though.....

[Duncan P]

Try something like

Code: Select all

SELECT [datefield], cast( datepart( mm, [datefield] ) as varchar ) as mm, cast( datepart( yy, [datefield] ) as varchar ) as yy FROM [tablename]

It works for me and comes back with no decimal point nor trailing zeros. Whether plain old varchar is the most efficient form is anyone's guess, but there it is.

I am using SQL Server 2005 and the SQL Native Client ODBC driver into TM1 9.5.2.

[ioscat]

it works! thank you, Duncan!