cancel
Showing results for 
Search instead for 
Did you mean: 

Creating calculated attribute "year" from "date"

Former Member
0 Kudos

Hello,

New to Hana so it might be a stupid question but I have a date attribute in my analytic view and I am trying to create a calculated attribute for 'year' based on the attribute "Date".

I thought it should be simple (and may be it is) but when I define the data type as NAVCHAR 4 and expression as "Date", I get the entire date instead of just first 4 characters. I tried expression leftstr("Date",4) but I get an error. Tried several different options Year("Date") etc. but nothing seems to work.

Can someone help me please?

regards,

Manoj

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

Hi Manoj,

to extract a part of the date information, please use the component-function.

component ( "DATEFIELD", 1)

will return a 4 character string containing the year-component of the data.

Just as it is documented 😉

- Lars

Former Member
0 Kudos

Thanks Lars. Though I ended up doing something like leftstr(string("DATE"), 4) which worked too.

regards,

Manoj Agrawal

lbreddemann
Active Contributor
0 Kudos

Ah ---- that's something you should avoid.

First of all, you cannot be sure about the actual format of the output of an date-to-string conversion.

Could be anything like

2001-12-02, 02/12/2001, 12-02-2001 ...

The other thing is, the component function is more efficient than the two stacked functions.

This will be visible if applied to lots of data.

- Lars

Former Member
0 Kudos

Hi Lars

  Can we get multiple componentes using the same function, Suppose if I have timestamp, from the timestamp , I want only the date , as of now we are using to_date,will the COMPONENT function holds good .

Thanks

Santosh

Former Member
0 Kudos

Hi Lars

  Can we get multiple componentes using the same function, Suppose if I have timestamp, from the timestamp , I want only the date , as of now we are using to_date,will the COMPONENT function holds good .

Thanks

Santosh

lbreddemann
Active Contributor
0 Kudos

Hi Santosh,

not really sure I got your question right.

COMPONENT() does allow to pick different components to be extracted from a data or a timestamp value.

See http://help.sap.com/hana/hana_dev_en.pdf#page=163 for the full function documentation.

- Lars

lbreddemann
Active Contributor
0 Kudos

Before I forget:

In any case, converting/extracting components from a data column is processing intensive and might require row-by-row work, depending on the setup.

With the build-in time system wide time tables (_SYS_BI.M_TIME_DIMENSION...) (http://help.sap.com/hana/hana_dev_en.pdf#page=163 you can solve the same taks via a join.

The system tables already contain extracted components and different date/time formats (if the data had been generated before).

That way, you can just omit single conversions/extractions and join in the date format you need.

- Lars

Former Member
0 Kudos

Hi Lars,

I tried using component function in the following manner:

select component('2009-03-04',1) from dummy;

But it is throwing an error   ---->    invalid function or procedure COMPONENT

Can you once execute this function and revert.

Thanks,

Sudipta

former_member182302
Active Contributor
0 Kudos

Hi Sudipta,

It is a CE function or you can use it in the calculated column.

Try this:

SELECT YEAR(CURRENT_DATE) FROM DUMMY; 


Lars has recently replied to the similar question in this thread, Have a look:

Regards,

Krishna Tangudu

Answers (1)

Answers (1)

Former Member
0 Kudos

what function to use if the date is being picked from ECC table??