Problem with Date format

building semantic layer
1

Hi All,

I have date field it is in MM/DD/YYYY HH:MS;SS AM/PM format , I want to modify this field in format of DD-MON-YYYY .

I tried with to_char(Date,‘DD-MON-YYYY’) , it is giving error . If it works also when I run the report I cant put filter on dates because this is character type .

Can some one please let me know how to get date field in format of DD-MON-YYYY and data type is Date .

Thanks in advance .

patriot3029 :india: (BOB member since 2006-12-08)

2

Just format the object at universe level - it’s a menu option in Designer.

Or see this topic:

Debbie

Debbie :uk: (BOB member since 2005-03-01)

3

Thanks for reply ,

I Can see format DD-MON-YYYY when I take object in Result Objects panel .

Proble is when I pull same object to Filter pane I am seeing different format (MM/DD/YYYY HH:MS:SS AM?PM) There I want to see same format DD-MON-YYYY .

Please some one let me know How to do that .

patriot3029 :india: (BOB member since 2006-12-08)

4

Use below code as a object definition with datatype date.

to_date(to_char(Date,'DD-MON-YYYY'),'DD-MON-YYYY')

Rakesh_K :india: (BOB member since 2007-12-11)

5

Create a filter in Universe designer

TRUNC(tabele_name.date_coumn,'DD') = to_date(@Variable('Enter date'),'DD-MON-YYYY')

Piotr.Caban :poland: (BOB member since 2009-01-19)

6

Thanks for reply ,

How to get List of values in Queery Filters pane in format of “DD-MON-YYYY”

patriot3029 :india: (BOB member since 2006-12-08)

7

Create dimension object with associated LOV.

Name: My Date
Type:Character
Select: to_char(table_name.data_col,'DD-MON-YYYY')

Create filter:

TRUNC(tabele_name.date_coumn,'DD') = to_date(@Prompt('Enter date',A,'Class name\My Date',MONO,FREE),'DD-MON-YYYY')

Piotr.Caban :poland: (BOB member since 2009-01-19)