BusinessObjects Board

Creating Date object, parse fails

Hi, wondering if anyone can provide some help. I’m in Designer trying to create a new object that looks at today’s date. When I try to use GetDate the parse fails with an “Incorrect Syntax” error. However, when I use an existing universe date object the parse works and I can’t understand why.

Here is what fails:
Case
WHEN DATENAME(dw,getdate()) = ‘Sunday’ THEN dateadd(dd,-7,convert(datetime,convert(char,getdate(),101)))
END

Here is what works:
Case
WHEN DATENAME(dw,@Select(Invoice Post Dates\Invc Post Date)) = ‘Sunday’ THEN dateadd(dd,-7,convert(datetime,convert(char,getdate(),101)))
END

The difference is the date being used in the DATENAME condition. The object “Invc Post Date” is a date object with a format of MM/DD/YYYY.

I have also tried doing “convert(datetime,convert(char,getdate(),101))” in the DATENAME section but this doesn’t work either.

Can’t understand what I’m doing wrong? Any help is definetly appreciated…

thanks!


nonyup (BOB member since 2008-01-07)

Objects that use getdate() will not parse because they don’t reference a table. As long as you use the object with at least one other “real” object in a query, the syntax will work properly.

See this Designer FAQ post for more details.


Dave Rathbun :us: (BOB member since 2002-06-06)

Oh, OK; I get it, that makes sense.

What I’m trying to do is create a beginning date object, (last Sunday example 3/18/2012) and an ending date, (this week’s Sunday example 3/25/2012) to use as start/end periods for a filter object.

The solution I came up with was to do a CASE statement with my universe object to come up with the beginning Sunday date, and then another CASE statement to come up with the ending Sunday date.

thanks for the help!!!


nonyup (BOB member since 2008-01-07)