OBIEE - Date Datatype and Functions

Bi Server Architecture With Client


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.


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


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))  

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

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

For the first week of the year

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

Previous Month


30 days before current date


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


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 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