SQL statement to derive Year and Month

[Former Member]

Hi,

I have a date field in a table with YYYY-MM-DD format. is it possible to get year and month in a separate columns using SQL statement.

[lbreddemann · Accepted solution]

Just for the sake of completeness...

Why don't you simply use the functions that SAP HANA has on-board for exactly this?

select

     extract(YEAR from purchase_date) "Year",

     extract(MONTH from purchase_date) "Month"

from your_table;

The extract function does exactly what you want and is quite expressive about what you want to do.

A quick test showed that both the extract function as well as the substring approach are processed late in the ResultAssembly node and that the excution times are fairly the same.

Given this, I would highly recommend to use the extract function as it makes it easier to specify what you want to get out of the source column while being self-descriptive about it.

Read this two years later and you will still know what is going on here.

- Lars

[Former Member]

Thanks Everyone. I am able to use  functions suggested by Patrick and Lars to get year and month

[patrickbachmann]

Excellent!  Glad to help and be mentioned in same sentence as Lars. 

[lbreddemann]

Well Former Member the mid-term goal for me is to get enough people like yourself or to work these forums, so that I can retire here

Worked just great in the Oracle/MaxDB forums (tip to the hat to !).

And I see more and more really good contributions here, so it's merely a question of time...

Just keep on with your work!

[patrickbachmann]

Thanks Lars!  If only we could just download your knowledge into our brains like The Matrix you could retire sooner.    But alas we want you to remain here!

[Former Member]

Hi Kris,

What is your use case for this need?

If it's for reporting, I'd recommend the following:

1) Generate time data in HANA and then join to your table. From time dimension you have pretty much any column you could ever needed related to time-based reporting. The doc should describe required steps:

2) If this is a one-off use case and absolute maximum performance is required, you might consider physically storing date components in their own columns rather than on-the-fly calculation. Here's a semi-relevant discussion on how to accomplish this concept with generated columns:

Cheers,

Jody

[former_member45129]

Hi Kumar,

Use Substring(value,starting position,length) Function to select that year and month as required.

Please go through List of function specific to your Back end database in IDT/UDT.

Regards,

Selva

[former_member184768]

How about

select left(purchase_date, 4) "Year", substring(purchase_date, 4,2) "Month" from your_table;

Regards,

Ravi

[patrickbachmann]

Hi Kris,

I would do something like this;

select PURCHASE_DATE, right(PURCHASE_DATE,2) as TheMonth, left(right(PURCHASE_DATE,4),2) as TheDay from TABLE.

If you want to have a textual description instead of the number then I would handle it via a case statement.

-Patrick