How to extract Month Value from Timestamp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

How to extract Month Value from Timestamp

Author

Topic

dirklx
Starting Member

13 Posts

Posted - 2007-11-23 : 09:20:49

I have a time dimension that consists out of timestamp fields. Now I like to extract a value for a given month e.g. 8 for August, 10 for October and such. How would I do that?

[Time].[STARTDATE].LastChild holds this value: 2007-10-01T00:00:00

Any help is highly appreciated.

Thanks in advance
Dirk

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-23 : 12:54:47

is that a datetime datatype?

Poor planning on your part does not constitute an emergency on my part.

dirklx
Starting Member

13 Posts

Posted - 2007-11-24 : 05:30:10

Yes, datetime.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-24 : 05:36:53

Use Month() function.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dirklx
Starting Member

13 Posts

Posted - 2007-11-24 : 07:42:02

Month() does not work. When I tried Year([Time].[STARTDATE].LastChild) I got 1905 as a result although
the year is 2007.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-24 : 07:48:35

Try using Datepart() function.

Datepart(month, [Time].[STARTDATE].LastChild)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dirklx
Starting Member

13 Posts

Posted - 2007-11-24 : 08:12:08

nope. Not working, I get an error message that month can't be found. Please keep in mind that I use the function as part of an MDX expression.

I calculated the YTD sales volume and now like to create a YTD mean value, therefore need to know the number of month in the current year. [YTD]/ value of month to be drawn from [Time].[STARTDATE].LastChild

I already tried:

CDate([Time].[STARTDATE].LastChild)
Month(CDate([Time].[STARTDATE].LastChild))
DatePart(month, [Time].[STARTDATE].LastChild)
....

Can it be that hard? This is something anybody needs I thought?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-24 : 12:17:01

Maybe

month(datediff(d,0,Time.[StartDate].LastChild ))

or read it as string and parse out the month's location?
Substring(cast(Time.[StartDate].LastChild as varchar),6,2) maybe?

Since you got 1905 as a result for the year, it could be a collation or default date format issue. Since dates are stored as integers and times as decimal, it would be hard for the standard datetime functions to not apply, but since you have an MDX expression that might be mucking it up too.

Poor planning on your part does not constitute an emergency on my part.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-24 : 21:23:23

Can use t-sql function in mdx query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources