This article contains all information and articles about the date datatype in OBIEE.
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'
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" ;
FILTER("Fact"."Revenue"
USING ("Time"."Cal Year" = year(current_date) and
"Time"."Cal Month"= month(current_date)))
FILTER("Fact"."Revenue"
USING ( YEAR("Time"."Calendar Date")= year(current_date) and
WEEK_OF_YEAR("Time"."Calendar Date") = 1 )
)
MONTH(TIMESTAMPADD(SQL_TSI_MONTH, -1, 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;