OBIEE - Date Datatype and Functions

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - About

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

Advertising

3 - Logical SQL

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

3.2 - Snippet

3.2.1 - 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" ;
Advertising

3.2.2 - Month To Date

FILTER("Fact"."Revenue" 
        USING ("Time"."Cal Year" = YEAR(CURRENT_DATE) AND 
               "Time"."Cal Month"= MONTH(CURRENT_DATE)))

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

3.2.4 - Previous Month

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

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