Derived table with prompt

building semantic layer
1

Hi All,

I need to find out First day and last day of month based on prompt value in Derived table. Below is the query to find out First day and last day on DB2. On the below query, instead of Current date, i’ve to use Prompt date. Can anyone please help on this.

select (current_date - day(current_date) day +1 day) AS firstday,
(current_date +1 month -day(current_date) day) AS lastday from sysibm.sysdummy1

When I use below query on Derived table, it is giving error.

select (@prompt(‘Enter Date :’,‘D’,Mono,Free)) - day(@prompt(‘Enter Date :’,‘D’,Mono,Free)) AS firstday
from sysibm.sysdummy1

Thanks.

krithi (BOB member since 2005-01-07)

2

Its because a prompt always return a char type value, you have to change it to date datatype
Try this

select CAST(@prompt('Enter Date :','D',,Mono,Free) AS DATE) - day(CAST(@prompt('Enter Date :','D',,Mono,Free) AS DATE) AS firstday 
from sysibm.sysdummy1

I have used cast function to show you the usage, you can see the correct date function in DB2
.

haider :es: (BOB member since 2005-07-18)