how to get year out of a date database field

[Former Member]

Hi,

I got a Oracle database field "createddate', i want get year from this field.

i Tried: to_date(to_char(createddate,'yyyy'),'yyyy')

i laso tried: trunc(createddate,'yyyy')

also tried: to_date(substr(to_chart(createddate),7,11,)'yyyy')

but when i test in Webi, i got '1/1/11' what's the right syntext?

Thanks

Edited by: hiccup on Dec 8, 2011 6:55 PM

[Former Member · Accepted solution]

You don't need to to_date() the result, as a year is not a date but either a numeric 2011 or character '2011' value. To extract the year from any date use

to_char(createdate,'YYYY')

only. If you want the result to be numeric, then use to_number() after you have extracted the year using the to_char() function.

Object formats only change the way something looks, they don't change the actual value, and should not be used for this.

[Former Member]

apply automatic hierarchy in the properties of your date object and it will create a year object

Edited by: PadawanGirl on Dec 12, 2011 3:38 PM

[amitrathi239]

hi,

one more thing if you right click on the universe object and apply format in the format object.

Thanks,

Amit

[Former Member]

Hi, Amit,

Actually I used the "object format", I added "yyyy" as a new "format" on my computer, then the Universe works perfect for the Webi report on my computer.

But after I export this Universe to the repository, when other developers used same universe, the Webi report just

didn't turn out nice.

What do i have to do? How to keep this new format coming cross?

Add "yyyy" to the server and modify each developer's computer? doesn't sound right.

Thanks

[former_member648661]

I think Universe has Year() function that can be used.