OBIEE - Date Datatype and Functions

About

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

Articles Related

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;