OBIEE - Date Datatype and Functions

1 - About

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

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

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;


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap