datepart function in SQL Server

building semantic layer
1

I have a report and it encorporates an object with the following filter dimension:

WHERE
(
…
…
( datepart(mm, tablename.Date) = datepart(mm, dateadd(mm, -1, GetDate())) )

When I run the report it doesn’t look like it is working and is bringing in everything, regardless if it matches or not.
But I may be missing something to this filter and having trouble figuring out what so far…

help

Joealyche (BOB member since 2012-02-29)

2

It is difficult to test via BO. Can you paste your query in a database query tool and test there?
It might be handy to split your condition in two parts and create two universe objects for it. Then drag them in your report to verify the values they show.

HenkK

HenkK :netherlands: (BOB member since 2004-03-02)

3

[Moderator Note: Moved from WebIntelligence XI to Semantic Layer / Universe Designer]

Marek Chladny :slovakia: (BOB member since 2003-11-27)

4

Rereading your post I see that you have an OBJECT with a where condition. You’d better create a filter in the universe with this condition and add it to your query.

HenkK

HenkK :netherlands: (BOB member since 2004-03-02)

5

I just added the Where clause showing that the filter I created in the Universe is being used in Query Filter section of the report.

So the added filter I created looks like this:

( datepart(mm, tablename.Date) = datepart(mm, dateadd(mm, -1, GetDate())) )

But reading it again I think I may be missing a piece or something.
If the datepart() of the tablename.date returns a 1(meaning the month is January) and
the datepart of the GetDate returns a one which running today will be January I think it would match fine within the Where filter.
Only I am returning ALL tablename.dates regardless if the month is January or August or December!?

Like I said if this didn’t work I think I need something in there or a different approach but drawing :hb:

Joealyche (BOB member since 2012-02-29)

6

Interesting; if I add the Month number to the Query it gets me closer to only bringing in the Month of January.

However, I am also bringining in January 2013, 2012, 2011. I ideally only want to bring in previous month of the time I am running the report so for the most part the years will be the same except if I run the report in January of 2015; the month should be December 2014.

Yet another thing to try and figure out. :hb: :hb:

Joealyche (BOB member since 2012-02-29)

7

How about:

Year(tablename.Date) * 100 + Month(tablename.Date) BETWEEN
Year(dateadd(mm, -1, GetDate())) * 100 + Month(dateadd(mm, -1, GetDate())) 
AND Year(GetDate()) * 100 + Month(GetDate())

So convert to a YYYYMM format to filter it with a BETWEEN.

HenkK

HenkK :netherlands: (BOB member since 2004-03-02)

8

Thanks for helping and allowing me to talk this out with you. I think I got it …

Using SQL Server as the syntax is written:

DATEADD(mm, DATEDIFF(m,0,tablename.DATE),0)
= DATEADD(mm,-1, DATEADD(mm,DATEDIFF(m,0,GETDATE()),0))

This will match rows where the month and year of the date in question(table.DATE) matches against the month and year using GetDate() when the report is run.

I think this will run as well on January 2015 !?

Now back to figuring out something with Business Objects Explorer, fun fun fun.

Thank you again

Joealyche (BOB member since 2012-02-29)