Need Help on Date Calculation

[anumala28]

Hi,

I need to display in the report first Monday date, second monday data...etc.,up to end of month, User will enter year and month only in the type in prompts.
Please help out...

Thanks,
Sri.

 

[bcking]

Just add a filter in the report that will return only mondays.

add this to your existing filter.

dayofweek(DATE) = 1

Where dayofweek is a function. And then sort the DATE field A - Z.

 

[anumala28]

I am not getting any records after applying this condtion.

I need to display only Monday dates. User will enter Year and Month.

ex: Year=2003 Monthe=3, I need to display Monday dates for the monthe 3.

If any logic please help out...

Thanks,
Sri.

 

[drlex]

Some dbs return 1 from DayofWeek for Sunday; try =2

"Time flies like an arrow, but fruit flies like a banana."

 

[bcking]

So your filter will have;

Year = YEARPROMPT and Month = MONTHPROMPT and dayofweek(DATE) = 1

Where the DATE fieled in the dayofweek function is the date of the record.

Do you have a date field for each record?

 

[anumala28]

No i don't have date filed. I need to use User entered Year and Month. I have field like YYYYMM that is string in the database.

Please help out...

Thanks,
Sri

 

[bcking]

Then how could you be expected to know which records are on a Monday or which are on Tuesday?

You will only know the month and year for each record, not each day. It would then be impossible to only show records on Monday.

 

[anumala28]

Can we create any calculated columns in the report. i don't want get detail records, but only dates i need to display in the first page of report(List Header).

If any logic please help out...

thanks,
Sri.

 

[nagrajm]

Is the date string in your database in the format YYYYMMDD because what the others are trying to tell you is that unless the date sting is of YYYYMMDD format you cannot determine what day of week the record pertains to.

If the date string in your database is missing the DD you cannot find the day of week unless you can get your DBA to get that information from the source database and modify the date column to include DD also. You may also consider, at the same time, converting the date column to date format instead of string to make your reporting life easier.

HTH

 

[GJParker]

anumala28

Do you want to filter your data to show only records for each monday of the month ?

or

are you trying to create a header that shows the dates of the mondays in a given month ?

Gary Parker
Systems Support Analyst
Manchester, England

 

[anumala28]

are you trying to create a header that shows the dates of the mondays in a given month ?

Yes I am trying to show that dates of the mondays in the list header.
Is it any logic?? Please help out...

Thanks,
Sri.

 

[nagrajm]

Here's a piece of sql that will help you find the date of the first monday of a given year and month.

TO_DATE(CONCAT(CONCAT(?Year?,?Month?),TO_CHAR(TRUNC(DECODE(TO_NUMBER(TO_CHAR(TO_DATE(CONCAT(CONCAT(?Year?,?Month?),'01') ,'YYYYMMDD' ) ,'D')),1,2 ,2,1,3,7,4,6,5,5,6,4,3) ) )) ,'YYYYMMDD' )

In impromptu, the calculation for first monday of the month and year will be given by;

First_Monday = to_date(concat(concat(?Year?, ?Month?),number-to-string(decode(or_dayOfWeek(to_date(concat(concat(?Year?, ?Month?),'01'),'YYYYMMDD'),1,2,2,1,,3,7,4,6,5,5,6,4,3))),'YYYYMMDD')

Subsequent mondays can be found by;

Second_Monday = add-days(First_Monday, 7)
Third_Monday = add-days(First_Monday, 14) etc.

I know the formula is very involved but it will work. The formula is based on the oracle return value of 1 for Sunday, so that Monday is 2. If the return value is different, modify the decode statement accordingly.

HTH