OBIEE - Date Datatype and Functions

Bi Server Architecture With Client

About

This article contains all information and articles about the date datatype in OBIEE.

Logical SQL

OBIEE - Logical Sql

Datetime Literals

The SQL 92 standard defines three kinds of 'typed' datetime literals, in the following formats:

DATE 'yyyy-mm-dd'
TIME 'hh:mm:ss'
TIMESTAMP 'yyyy-mm-dd hh:mm:ss'

To express a typed datetime literal, use the keywords DATE, TIME, or TIMESTAMP followed by a datetime string enclosed in single quotation marks, as in the preceding example. Two digits are required for all nonyear components even if the value is a single digit.

These formats are fixed and are not affected by the format specified in the NQSConfig.INI file for the parameters DATE_DISPLAY_FORMAT, TIME_DISPLAY_FORMAT, or DATE_TIME_DISPLAY_FORMAT.

Examples

DATE '2000-08-15'
TIME '11:55:25'
TIMESTAMP '1999-03-15 11:55:25'

Snippet

Week of the month

mod(WEEK_OF_YEAR("D0 Time"."T00 Calendar Date"),
    CASE WHEN month("D0 Time"."T00 Calendar Date") >= 10  
      THEN WEEK_OF_YEAR( cast( cast(year("D0 Time"."T00 Calendar Date") as char) || '/' || 
                         cast(month("D0 Time"."T00 Calendar Date") as char) || '/01' as date))
      ELSE WEEK_OF_YEAR( cast( cast(year("D0 Time"."T00 Calendar Date") as char) || '/0' || 
                         cast(month("D0 Time"."T00 Calendar Date") as char) || '/01' as date))  
    END
)

In this forumla, we assume for the cast function that in the nqsconfig, you have the date format specified as :

DATE_TIME_DISPLAY_FORMAT = "yyyy/mm/dd hh:mi:ss" ;

Month To Date

FILTER("Fact"."Revenue" 
        USING ("Time"."Cal Year" = year(current_date) and 
               "Time"."Cal Month"= month(current_date)))

For the first week of the year

FILTER("Fact"."Revenue" 
      USING ( YEAR("Time"."Calendar Date")= year(current_date) and
              WEEK_OF_YEAR("Time"."Calendar Date") = 1 )
)

Previous Month

MONTH(TIMESTAMPADD(SQL_TSI_MONTH, -1, CURRENT_DATE))

30 days before current date

TIMESTAMPADD(SQL_TSI_DAY, -30, CURRENT_DATE)

TimestampAdd is an ODBC function that is included normally in the ODBC scalar function set

SELECT {fn TIMESTAMPADD (SQL_TSI_DAY, 1, {fn NOW()})} FROM DUAL;





Discover More
Obiee Dashboard Prompt Type Presentation Variable
OBIEE - Datatype of a presentation variable

Each variable in an application have a scope and a data type. When you set a presentation variable with a dashboard prompt, the presentation variable takes the data type of the value in the column...
Obiee Filter On Date
OBIEE - Date Format in presentation variable, dashboard prompt and logical SQL

This article talk the manipulation of a presentation variable with a date datatype. Starting with OBIEE 10.1.3.4.1 and higher versions, Dashboard Prompt input formats and presentation variable values...
Obiee Repository Variable Date Type
OBIEE - How to set and use a server variable (system/repository) of type date ?

This article is a tutorial on how to set a date in a server variable (session variable of repository variable) and use it. This article use the schema SH : SH repository installation in the database and...
Obiee Ceim
OBIEE - Logical Sql

The logical SQL is the SQL that understand BI Server. It's a full Ansi 92 SQL (including subqueries and derived tables), plus special functions (SQL extensions) like AGO, TODATE, EVALUATE, and others....
Time - Time representation (Moment, Instant)

This section goes over the representation of time in a scalar way versus in a collection way (ie time series) Time is basically a numeric counter that increases over time. An instant in time can be represented...



Share this page:
Follow us:
Task Runner