Current Date-7 in DB2 Database.

building semantic layer
1

Hi,
Trying to get the current date-7 in DB2 and getting WIS 10901 /SQLSTATE 42816 error. How to get the current date -7 days in DB2.

looksmee :uk: (BOB member since 2006-02-08)

2

You need the DAYS keyword:-

CURRENT DATE -7 DAYS

Mak 1 :uk: (BOB member since 2005-01-06)

3

Hi Mak,

You saved my day and this is the first day am started with DB2 DB.

Thanks again.

Regards,
Siva.M

looksmee :uk: (BOB member since 2006-02-08)

4

Great!
However, I suggest you read up on DB2 syntax.
As you can see it is quite different to most other types of SQL syntax…

Mak 1 :uk: (BOB member since 2005-01-06)

5

Along these same lines, I’m trying to get the next 5 weeks from the prompted date, in DB2. As far as I know, this syntax is valid:

D_CALENDAR.CAL_DATE >= @Prompt('Select Order Date', 'D',, Mono, free) AND D_CALENDAR.CAL_DATE <= @Prompt('Select Order Date', 'D',, Mono, free) + 35 DAYS

It parses fine, until I add the “+ 35 DAYS” to it. Then it chokes:

Any clues?

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

6

Does this work?

D_CALENDAR.CAL_DATE >= @Prompt('Select Order Date', 'D',, Mono, free) 
AND D_CALENDAR.CAL_DATE -35 DAYS <= @Prompt('Select Order Date', 'D',, Mono, free)

Andreas :de: (BOB member since 2002-06-20)

7

Yes, it does. Thanks. 8)

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

8

I guess it is a problem related to how BusinessObjects converts the String to a date for the @PROMPT function…

Andreas :de: (BOB member since 2002-06-20)