Is this possible?

Lets say I have an integer value 2002012, I want to convert it to a string so I can cut the value 4 spaces so that it reads 2002 and then convert it back to an int to add one to it. The name of the field is PERIOD.

CONVERT(Int, SUBSTRING(CAST(PERIOD As Varchar),1,4)) + 1

Is that possible at all or will I get an error?

Is that possible at all or will I get an error?

Give a try, then. It's easy, isn't it?

|||

This is also possible

declare @.i int
select @.i = 2002012

select left(@.i,4) +1

they will both work see below

declare @.i int
select @.i = 2002012

select left(@.i,4) +1,CONVERT(Int, SUBSTRING(CAST(@.i As Varchar),1,4)) + 1

Denis the SQL Menace

http://sqlservercode.blogspot.com/

Is this possible?

Hi guys,

Is there any mechanism or tool to convert T-SQL query to its corresponding MDX query?

Please let me know.

Sincerely,

Amde

That begs the question: is there necessarily a corresponding MDX query? So if you could explain the context, or what problem you're trying to solve, that would help.|||

Hi,

The thing is I have a T-SQL query which works perfectly in a relational database. And I want to implement the same functionality in my Cube. So I am curious to know if I could achieve this thing.

Sincerely,

Amde

|||

Hi Amde,

Can you give an idea of the cube and how it is built from relational data? Also, what values does the T-SQL query return, or what does the query look like?

|||

hi,

Basically, I am working on repoting service, and I don't know how the cube is built. All I know is that I have the dimesions, measures, levels, members and so on to generat the report. But, I can tell you about the T-sql query and you can give whether I can achieve the same functionality using MDX query?

The t-sql query do some calculation on the date information and keep on storing the values in the temporary table. Finally, these value will be used in the report.

Sincerely,

Amde

|||Well, knowing the T-SQL would help (sounds like it's more than just queries, if temp tables are inolved). But it would be hard to figure out the MDX without knowing something about the cube design - how do the dimensions and measures relate to the report you're trying to generate, for example?|||

By the way, is it possible to create a temporary table in cubes?

|||Not exactly, but depends on what overall problem you're trying to solve - I think it's useful to understand the capabilities of Analysis Services OLAP in its own right, versus just drawing detailed comparisons to relational concepts.|||

Okay, assume the following scenario:

I have a date dimension, which stores date info. Assume I want to select the members of the dimension , for example, based on this condition: [Dates].[Date].&[2006-02-25T00:00:00]:[Dates].[Date].&[2006-06-24T00:00:00]. And I want the output in the following format;i.e. on a monthly basis.

From 2006-02-25 to 2006-03-24

From 2006-03-25 to 2006-04-24

From 2006-04-25 to 2006-05-24

From 2006-05-25 to 2006-06-24

How can I achieve this scenario?

Sincerely,

Amde

|||

A couple of questions:

Is 24th the end of the fiscal or reporting month - in which case create a separate "fiscal month"?|||

Hi,

Here is some answer inline:

-"Fiscal month" is not necessary for my report, because of the fact that the report is not financial related.

-Yes there will be a measure which counts some values on each date range.

Sincerely,

Amde

sql

Is this bug with Convert?

I was trying to debug some DateTime.Now in a C# project and while debugging I found this.

In your Sql Management Studio, type this:

The 916 becomes 917. Why does my millisecond get screwed?

select convert(datetime, '2007-06-29 15:22:31:921') -- prints 2007-06-29 15:22:31.920

select convert(datetime, '2007-06-29 15:22:31:916') -- print 2007-06-29 15:22:31.917

That is because of the precision of the datetime data type 1/300 of a second. Check BOL for more info about datetime data type.

select convert(datetime, '2007-06-29 15:22:31:998')

go

AMB

is there any way to convert the result of an FOR XML EXPLICIT into a varcha(1000) in tsq

is there any way to convert the result of an FOR XML EXPLICIT into a
varcha(1000) in tsql?
In SQL 2000 it is impossible. In SQL 2005 you can easily achieve that using
FOR XML in sub-query syntax:
SELECT CONVERT(VARCHAR(1000),(SELECT 1 tag, 0 parent, 1 'elt!1!' FOR XML
EXPLICIT))
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and
confers no rights.
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:eBrowdrbEHA.3864@.TK2MSFTNGP10.phx.gbl...
> is there any way to convert the result of an FOR XML EXPLICIT into a
> varcha(1000) in tsql?