Attribute cast date as character to filter but lose time zone preference.

Kimberly Showman

Organization Name (Required - If you are an Oracle Partner, please provide the organization you are logging the idea on behalf of): CCF

Description (Required):

I have an OTBI query that looks for invoices created yesterday, it was advised to cast the date as character in order to look back one day, example below. The issue is when this date field is changed to character we lose the user preference for time zone settings on the date field. Since the default timezone is UTC time the report output is inaccurate . See attaching White Paper, I could not locate a more recent version.

SUBSTRING(CAST("- General Information"."Invoice Entered Date" AS CHAR(40)) FROM 1 FOR 10) = SUBSTRING(CAST(timestampadd(SQL_TSI_DAY,-1,CURRENT_DATE) AS CHAR(40)) FROM 1 FOR 10)

I'm trying to find a way to search invoices created yesterday while retaining the date format in this filed.

WorkingWithTimeZonesWhitePaper.pdf

Use Case and Business Need

Reporting is off due to time zone default of UTC when date field is cast as a character field.

Enhancement Request / Service Request:

Need the ability to filter a date field by the prior day (or other number of days, weeks, months, etc) without casting the field as character.