Days in a month

building semantic layer
1

Hi,

I have a date (with time stamp) column in a table. How do I get the # of days in a month?

For example:
Date
02/12/2001 08:50:41
02/12/2001 10:22:47
02/12/2001 11:06:13
02/12/2001 12:20:49
02/12/2001 14:15:02
02/13/2001 06:42:15

I want to get the # of days. In above data I should get 2 days. Can anyone help please?
:expressionless: :?

Thanks
Candy

eye_candy :us: (BOB member since 2005-03-18)

2

Hi,
Try

=DaysBetween(Min(<Timestamp(Month Dataset)>) ,Max(<Timestamp(Month Dataset)>))+1

Thanks
-Satish

forgotUN (BOB member since 2006-12-13)

3

You can also try using truncate
for eg.

Count(trunc(month_object)

gaurav S (BOB member since 2010-04-22)

4

Candy,

What RDBMS are you on? The syntax varies from Oracle to SQL Server to DB2 etc.

Cheers,
Mark

5

Hi Mark,

Oracle DB.

Thanks
Candy :cookie:

eye_candy :us: (BOB member since 2005-03-18)

6

Create a variable which just represents the YEARMONTH portion of your date (i.e. FormatDate (,“YYYYMM”) ). Then you can use something like: =Count(FormatDate(,“YYYYMMDD”))

That should ignore the time portion and count the number of distinct dates. If you are, instead, asking how to determine how many days there are in the month (versus how many distinct days were returned), in oracle you can use something like: to_char(last_day( table.dateobjectcolumn ),‘DD’)

digpen :us: (BOB member since 2002-08-15)

7

Hi Digpen,

Thanks for the reply.

I want to calculate days in a date column. Not the days in a month.
I have date column(mostly working days). I need to count the working days in a particular month and year from the column.

Hope this makes sense?

Thanks
Candy :? :?

eye_candy :us: (BOB member since 2005-03-18)

8

Did the first half of my initial response not help in that regard?

When you say work days, are you looking to only count M-F days (business days), or just the distinct days that are returned? If the latter, then you should have all of the pieces to do so from both a data provider method:

count( distinct trunc( table.column))

And business objects desktop intelligence version:

=count( formatdate( <dateobjwithtimestamp>, "YYYYMMDD"))

digpen :us: (BOB member since 2002-08-15)