Skip to Content
-1

Convert in hana

Feb 20, 2017 at 07:56 AM

116

avatar image

Hi all,

How to convert a TIMESTAMP to BIGINT in HANA studio?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Lars Breddemann
Feb 22, 2017 at 11:10 AM
0

Do you realise that the conversion from a date data type into an integer is not a standardised operation?

MS SQL Server clearly does that differently and that's fine.

There is no standard for that.

If you have a specific use case in mind that requires the result to be like the one from SQL Server, then you might need to write your own little function for that. It's not that difficult.

My guess would be that SQL Server returns the number of days since 1st January 1901 for their cast to an integer.

You can emulate that very easily in SAP HANA by using the add_days() function:

select days_between('1900-01-01', '2017-02-22') from dummy;
DAYS_BETWEEN('1900-01-01','2017-02-22')
42786

There you go.

Happy with that?

It's important to understand, what the functions actually do and which ones are standard and which ones are vendor specific.

Share
10 |10000 characters needed characters left characters exceeded
Lars Breddemann
Feb 21, 2017 at 09:26 AM
0
select
    tstmp_from_seconddate(current_timestamp)
from dummy;
TSTMP_FROM_SECONDDATE(CURRENT_TIMESTAMP) 
20170221092514                          

This should do the trick...

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Lars,

Thank you for reply,

You'r answer is true, but this result isn't equal with SQL result.

Please see sample:

SQL:

select getdate() --result: 2017-02-22 11:31:00.787
select cast(getdate() as bigint) --result: 42786

HANA:

select (current_timestamp)from dummy; --result: Feb 22, 2017 11:37:20.095 AM

select tstmp_from_seconddate(current_timestamp) from dummy; --result:20,170,222,113,720

0

Thanks lars;

I changed my function and solved my problem by another way.

0

you might as well accept the answer instead of closing the question for "other" reasons.

0