Calculating no of days in a month from Presentation variable ?

Question

Hi  Gurus,

My requirement : One of my user enters a date which I will capture into a presentation variable v_date. Is there a way  to calculate number of days in the v_date variable (or) the date entered by the user in OBIEE ?

TIA

Venkat

Robert Angel · Answer

You might find this a useful reference; -

Functions
:

Current_Date
 Current_Time

Current_TimeStamp

Day_Of_Quarter

DayName

DayOfMonth

DayOfWeek

DayOfYear

Hour

Minute

Month

Month_Of_Quarter

MonthName

Now

Quarter_Of_Year

Second

TimestampAdd

TimestampDiff

Week_Of_Quarter

Week_Of_Year

Year

Current_Date

Returns
the current date. The date is determined by the system in which the Oracle BI
Server is running.

Syntax

Current_Date

Current_Time

Returns
the current time. The time is determined by the system in which the Oracle BI
Server is running.

Note: The Analytics Server does not cache queries that contain this
function.

Syntax

Current_Time(integer)

Where:

integer

Any
integer that represents the number of digits of precision with which to display
the fractional second.

Current_TimeStamp

Returns
the current date/timestamp. The timestamp is determined by the system in which
the Oracle BI Server is running.

Note: The Oracle BI Server does not cache queries that contain this
function.

Syntax

Current_TimeStamp(integer)

Where:

integer

Any
integer that represents the number of digits of precision with which to display
the fractional second.

Day_of_Quarter

Returns
a number (between 1 and 92) corresponding to the day of the quarter for the
specified date.

Syntax

Day_Of_Quarter(dateExpr)

Where:

dateExpr

Any
expression that evaluates to a date.

DayName

Returns
the name of the day for a specified date.

Syntax

DayName(dateExpr)

Where:

dateExpr

Any
expression that evaluates to a date.

DayOfMonth

Returns
the number corresponding to the day of the month for a specified date.

Syntax

DayOfMonth(dateExpr)

Where:

dateExpr

Any
expression that evaluates to a date.

DayOfWeek

Returns
a number between 1 and 7 corresponding to the day of the week, Sunday through
Saturday, for a specified date. For example, the number 1 corresponds to Sunday
and the number 7 corresponds to Saturday.

Syntax

DayOfWeek(dateExpr)

Where:

dateExpr

Any
expression that evaluates to a date.

DayOfYear

Returns
the number (between 1 and 366) corresponding to the day of the year for a
specified date.

Syntax

DayOfYear(dateExpr)

Where:

dateExpr

Any
expression that evaluates to a date.

Hour

Returns
the number (between 0 and 23) corresponding to the hour for a specified time.
For example, 0 corresponds to 12 A.M. and 23 corresponds to 11 P.M.

Syntax

Hour(timeExpr)

Where:

timeExpr

Any
expression that evaluates to a time.

Minute

Returns
the number (between 0 and 59) corresponding to the minute for a specified time.

Syntax

Minute(timeExpr)

Where:

timeExpr

Any
expression that evaluates to a time.

Month

Returns
a number (between 1 and 12) corresponding to the month for a specified date.

Syntax

Month(dateExpr)

Where:

dateExpr

Any
expression that evaluates to a date.

Month_Of_Quarter

Returns
the number (between 1 and 3) corresponding to the month in the quarter for a
specified date.

Syntax

Month_Of_Quarter(dateExpr)

Where:

dateExpr

Any
expression that evaluates to a date.

MonthName

Returns
the name of the month for a specified date.

Syntax

MonthName(dateExpr)

Where:

dateExpr

Any
expression that evaluates to a date.

Now

Returns
the current timestamp. This function is equivalent to the function
current_timestamp .

Syntax

Now()

Quarter_Of_Year

Returns
the number (between 1 and 4) corresponding to the quarter of the year for a
specified date.

Syntax

Quarter_Of_Year(dateExpr)

Where:

dateExpr

Any
expression that evaluates to a date.

Second

Returns
the number (between 0 and 59) corresponding to the seconds for a specified
time.

Syntax

Second(timeExpr)

Where:

timeExpr

Any
expression that evaluates to a time.

TimestampAdd

Adds
a specified number of intervals to a specified timestamp, and returns a single
timestamp. Passing a null intExpr or timeExpr to this function results in the
return of a null value.

In the simplest scenario, this function simply adds the specified integer value
to the appropriate component of the timestamp, based on the interval. Adding a
week translates to adding seven days, and adding a quarter translates to adding
three months. A negative integer value results in a subtraction (such as going
back in time).

An overflow of the specified component (for example, more than 60 seconds, 24
hours, twelve months, and so on) necessitates adding an appropriate amount to
the next component. For example, when adding to the day component of a
timestamp, this function makes sure that overflow takes into account the number
of days in a particular month (including leap years). Similar measures are used
to make sure that adding a month component results in the appropriate number of
days for the day component (such as adding a month to '2010-05-31' does not
result in '2010-06-31' because June does not have 31 days). The function also
deals with the month and day components in a similar fashion when adding or
subtracting year components.

Syntax

TimestampAdd(interval,
intExpr, timestamp)

Where:

interval

The
specified interval. Valid values are: SQL_TSI_SECOND, SQL_TSI_MINUTE,
SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER,
SQL_TSI_YEAR.

intExpr

Any
expression that evaluates to an integer value.

timestamp

Any
valid timestamp.

Examples

Select
{TimestampAdd(SQL_TSI_DAY, 3, TIMESTAMP'2000-02-27 14:30:00')}

From Employee where employeeid = 2;

In
the above example, the query asks for the resulting timestamp when 3 days are
added to '2000-02-27 14:30:00'. Since February, 2000 is a leap year, the query
returns a single timestamp of '2000-03-01 14:30:00'.

Select
{TimestampAdd(SQL_TSI_MONTH, 7, TIMESTAMP'1999-07-31 00:00:00')}

From Employee where employeeid = 2;

In
the above example, the query asks for the resulting timestamp when 7 months are
added to '1999-07-31 00:00:00'. The query returns a single timestamp of
'2000-02-29 00:00:00'. Notice the reduction of day component to 29 because of
the shorter month of February.

Select
{TimestampAdd(SQL_TSI_MINUTE, 25, TIMESTAMP'2000-07-31 23:35:00')}

From Employee where employeeid = 2;

In
the above example, the query asks for the resulting timestamp when 25 minutes
are added to '2000-07-31 23:35:00'. The query returns a single timestamp of
'2000-08-01 00:00:00'. Notice the propagation of overflow through the month
component.

TimestampDiff

Returns
the total number of specified intervals between two timestamps. Passing a null
timestamp to this function results in a null return value.

This function first determines the timestamp component that corresponds to the
specified interval parameter, and then looks at the higher order components of
both timestamps to calculate the total number of intervals for each timestamp.
For example, if the specified interval corresponds to the month component, the
function calculates the total number of months for each timestamp by adding the
month component and twelve times the year component. Then the function
subtracts the first timestamp's total number of intervals from the second
timestamp's total number of intervals.

The TimestampDiff function rounds up to the next integer whenever fractional
intervals represent a crossing of an interval boundary. For example, the
difference in years between '1999-12-31' and '2000-01-01' is 1 year because the
fractional year represents a crossing from one year to the next (such as 1999
to 2000). By contrast, the difference between '1999-01-01' and '1999-12-31' is
zero years because the fractional interval falls entirely within a particular
year (such as 1999). Microsoft's SQL Server exhibits the same rounding
behavior, but IBM's DB2 does not; it always rounds down. Oracle does not implement
a generalized timestamp difference function.

When calculating the difference in weeks, the function calculates the
difference in days and divides by seven before rounding. Additionally, the
function takes into account how the administrator has configured the start of a
new week in the NQSConfig.ini file. For example, with Sunday as the start of
the week, the difference in weeks between '2000-07-06' (a Thursday) and
'2000-07-10' (the following Monday) results in a value of 1 week. With Tuesday
as the start of the week, however, the function would return zero weeks since
the fractional interval falls entirely within a particular week. When
calculating the difference in quarters, the function calculates the difference
in months and divides by three before rounding.

Oracle BI Server pushes down the TIMESTAMPADD and TIMESTAMPDIFF functions to
Microsoft's SQL Server and ODBC databases by default. While Oracle BI Server
can also push to IBM's DB2, the features table is turned off by default due to
DB2's simplistic semantics. (IBM's DB2 provides a generalized timestamp
difference function, TIMESTAMPDIFF, but it simplifies the calculation by always
assuming a 365-day year, 52-week year, and 30-day month.) The features table is
also turned off by default for Oracle, since Oracle databases do not fully
support these functions.

Syntax

TimestampDiff(interval,
timestamp1, timestamp2)

Where:

interval

The
specified interval. Valid values are: SQL_TSI_SECOND, SQL_TSI_MINUTE,
SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER,
SQL_TSI_YEAR.

timestamp1

Any
valid timestamp.

timestamp2

Any
valid timestamp.

Examples

Select
{TimestampDiff(SQL_TSI_DAY, TIMESTAMP'1998-07-31 23:35:00',
TIMESTAMP'2000-04-01 14:24:00')}

From Employee where employeeid = 2;

In
the above example, the query asks for a difference in days between timestamps
'1998-07-31 23:35:00' and '2000-04-01 14:24:00'. It returns a value of 610.
Notice that the leap year in 2000 results in an additional day.

Week_Of_Quarter

Returns
a number (between 1 and 13) corresponding to the week of the quarter for the
specified date.

Syntax

Week_Of_Quarter(dateExpr)

Where:

dateExpr

Any
expression that evaluates to a date.

Week_Of_Year

Returns
a number (between 1 and 53) corresponding to the week of the year for the
specified date.

Syntax

Week_Of_Year(dateExpr)

Where:

dateExpr

Any
expression that evaluates to a date.

Year

Returns
the year for the specified date.

Syntax

Year(dateExpr)

Where:

dateExpr
Any expression that evaluates to a date.

Thomas Dodds · Answer

Hence the importance of a properly formed calendar dimension where every date has these types of attribute just laid down and there's no on-the-fly calculating ... very simple in ETL/ELT to do it and then no matter what date a use picks a simple lookup to the dimension returns the desired start, end, and days in between.  Built it once and use it infinitely.

As @Christian Berg has said, it's pretty straightforward ... here's a start:

First Day of Month
TIMESTAMPADD(SQL_TSI_DAY, -DAYOFMONTH(CURRENT_DATE) +1 , CURRENT_DATE)

Last Day of Month
TIMESTAMPADD(SQL_TSI_DAY, -DAY(TIMESTAMPADD(SQL_TSI_MONTH, 1, CURRENT_DATE)), TIMESTAMPADD(SQL_TSI_MONTH, 1, CURRENT_DATE))

[Deleted User] · Answer

Well yeah of course...you can't calculate a time range with one date...

But you said "Calculating no of days in a month", so it's pretty obvious that the start of the comparison has to be the first day of the month. Simple logic.