Table of Contents

OBIEE - Date Datatype and Functions

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;