Skip to Content

Calendar month and year from date in HANA

Hi,

I created analytical view on the sales table VBAP and vbak.

Following are my requirements

1. I have a created date in vbak and I need to derive Calendar month and calendar year from the date..How can i do that in HANA??

2. I have actual delivery date and GR dates in the purchasing tables. Now i need to calculate the days difference in HANA. I tried daysbetween function but its not working.

3. After looking at the raw data in data preview. Is it possible to remove columns. I mean slice and dice?

Thanks
Sri

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Jan 22, 2015 at 09:50 AM
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 21, 2015 at 09:31 AM

    Hi Sri,

    1) Alternative to Safiyudeen's solution you can also try component function in calculated column.

    component(date,x) x=1 for year(yyyy), x=2 for month(mm) and x=3 for date(dd).

    But for that date format should be yyyy-mm-dd.

    If your date format is not as above mentioned, there is a workaround.

    Say your date is 20-06-2014 (dd-mm-yyyy).

    You can create a calculated column (say, new_date and data type should be date)

    Use below formula

    date(rightstr(string('20-06-2014'),4)+'-'+midstr(string('20-06-2014'),4,2)+'-'+leftstr(string('20-06-2014'),2))

    Now new_date is in desired date format (yyyy-mm-dd)

    Use this new_date calculated column in the component function to fetch calender year and month.

    2) Daysbetween function will only work if your date format is 'yyyy-mm-dd'. Can you please check what is the error you are getting. If the format is different you can use above workaround.

    3) As said by Safiyudeen, you can mark it hidden in semantics or you don't add to output un-necessary columns in analytic view

    Regards,

    Sumit

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 21, 2015 at 07:54 AM

    Hi sri,

    1) You can derive month and year by creating a time dimension table by generating time data and then joining your date field to this table.

    2)To calculate days difference you can use daysbetween function.

    Note: The field's data type must be date. To be on the safer side try typecasting to longdate(). something like this

    daysbetween(longdate(2012-01-01),longdate(2014-01-01))

    3)If you mean to play with output columns, you can make use of analysis tab in the data preview. Where you can select your desired columns for the output.

    if you mean to restrict or hide some columns you can choose to hide the columns by checking hidden in semantics.

    Hope this helps you.

    Regards,

    Safiyu

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 13, 2016 at 06:29 PM

    I was also stuck in similar situation : This is how I have solved this problem :

    Year is actually derived from DATE which is of the following format 2011-01-01.

    Output :

    Hope this helps.


    Capture.PNG (18.0 kB)
    Capture.PNG (30.7 kB)
    Capture.PNG (14.0 kB)
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.