Anyone know how to convert decimal to timestamp on hana studio?

[former_member242283]

hi all,

I tried to create a calculated attribute to convert a decimal type record to timestamp.

here is what the original data looks like:　 20,130,302,230,000

in the calculated column dialog box,  I select TIMESTAMP as data type.

in the expression editor , i used this method :  longdate(ltrim(string("TST_TO"),'.'),'YYYYMMDD"T"HH24MISS')

You can refer to my screenshot. However , it didn't work. when I tried to have a data preview ,  it gave out an error message,

" Could not execute 'select * from "_SYS_BIC"."dtrc/AT_IC_RCMDED_DEL_DATE"' in 205 ms 408 µs .

[303]: invalid DATE, TIME or TIMESTAMP value: search table error:  [6931] attribute value is not a date or wrong syntax "

I searched google about the timestamp definition , longdate, sql script manual , and tried many ways like thousand times,  still can't figure out

what's wrong with the conversion.

after get the timestamp, i'd like to use localtoutc functions to convert it to UTC time.

Can anyone help ?

PS. my hana db is revision 61.

Thanks

Amon

[Former Member · Accepted solution]

Hi,

I do not know if this problem is solved or not, I had the same problem and got it worked as:

CAST(LPAD(cast("VALID_FROM" as NVARCHAR),14,'0') AS timestamp)

Thanks

Anees

[Former Member]

This message was moderated.

[Former Member]

I had faced the same issue on the view. Although, I initially attributed this to the conversion in the calculated attribute formula, later on identified the root cause to be incorrectly defined filter on a different attribute.

The filter on a date field on the view , DATEFIELD1 was defined as EQ(NULL) instead of IS NULL.

[Former Member]

Hi Amon,

The usage of longdate seems to be faulty.

Can you try

1. Convert decimal into string(The method you are using will work for that)

2. Using the midstr() or any other string function to cut the string into parts

3. Storing the results into different columns where you have the Year, Month, Day, Hour, Minute and Seconds.( You may decide to hide these columns from output)

4. Then use these to combine into date with the function date() into one Final Date Calclated Column.

Refer the developer guide Section 5.4.5 available here http://help.sap.com/hana_appliance/ for function usage.

--Shreepad

[former_member242283]

Hi Shreepad,

Thanks for ur help, finally got the usage for all the functions in the expression editior.

I took your advice,  I created one calculated column to convert decimal to string.

      CA_IC_TST_STR =  ltrim(string("TST_TO"),'.')

the other column to covert it to time stamp, then another quetions occurs to me.

How should I convert the string back to integer type?

I even tried with some lucky using this expressing 

date(int(leftstr("CA_IC_TST_STR",4)),int(midstr("CA_IC_TST_STR",5,2))) though it retured numeric overflow error.

Maybe I got think about a better algorithm using limited functions provided in the expressiong editor.

I would be really appreciated if you can provide some brilliant idea.

Thanks and best regards,

Amon

[Former Member]

Hi Amon,

I am sorry for late reply, I was caught up with some work. I hope that you have already found a solution.

After successfully converting to string and getting the output in CA_IC_TST_STR,

Create YEAR, MONTH..MINUTES, SECONDS Calculated Attributes with datatype as INTEGER

The formula would look like

YEAR  = int(midstr(CA_IC_TST_STR,1,4))

MONTH  = int(midstr(CA_IC_TST_STR,5,2))

DATE  = int(midstr(CA_IC_TST_STR,7,2))

HOURS = int(midstr(CA_IC_TST_STR,9,2))

MINUTES =  int(midstr(CA_IC_TST_STR,11,2))

SECONDS  = int(midstr(CA_IC_TST_STR,13,2))

Then the next step could be another Calc Column with datatype as TIMESTAMP

TIMESTAMP = longdate("YEAR","MONTH","DATE","HOURS","MINUTES","SECONDS")

This column can give the output with Timestamp datatype as is the requirement.

The solution you are trying is to write everything in one single formula works fine, but things get confusing, so I prefer this way where you can have full visibility.

You can always hide the columns which are not required in the final output.

--Shreepad

[former_member242283]

Hi Shreepad,

Really thank u for ur kind help.

Your solution is much more easier to read than mine.

However, convert year,month,day etc. doesn't  solve the numeric overflow issue.

when I created all thoese  calculated attributes like you propose in my hana environment,  the attribute view can be activated successfully ,but throw numeric overflow error " search table error:[6994] attribute

engine : overflow in numeric calculation."

Conversion from string to int might not be easy in the column expression editor.

Actually I already decided to do this timestamp conversion in the procedure.

Anybody who can come up with the idea about the conversion to timestamp in the expression editor,

hope you can join this disscussion.

Thanks,

Amon

[Former Member]

Hi Amon,

Few questions:

1. Whay are you using LTRIM for '.' ? i suppose you are getting some of the entries in this column starting with '.' But if that is not the case then you can avoid LTRIM, STRING function which you have used.

2. Does the column TST_TO have all the entries with 14 digits (as you have mentioned 20,130,302,230,000) ? if this is having all the entries with excatly 14 digits (no more, no less) then why dont you directly write LONGDATE("TST_TO").

Or say after trimming the '.' if you get exactly 14 digits then you can use

LONGDATE(LTRIM(STRING("TST_TO"),'.'))

3. If your column is not having 14 digits after removing the '.' then please use the method mentioned by Shreepad in his above post. But you need to make sure that none of the components return a value which can not be converted into integer e.g. a blank space, a blank string, any special character etc. I mean all the MIDSTR() components should return a number which the INT() function can use.

The first checkpoint for you would be to find out if you have all entries in the column with 14 digits. If that is maintained then you need go through all these convoluted process 🙂

Regards,

Piyush

[Former Member]

Hi Amon,

I found a simpler way to do it,

1. Without converting the decimal to string in the first step.

2. Create just one calculated column with the formula

     longdate("TST_TO")

Make sure that the datatype of this column is TIMESTAMP

I have checked this

Can you try this one? Its a lot simpler

I am sorry I couldn't come up with this one earlier and wasting your time.

But the error that you're getting, about numeric overflow, could actually point to some other error in the calculation view. Please check if you have some other Measure where the datatype cannot handle the result.

--Shreepad

[former_member242283]

Hi Piyush,

I have already tried converting decimal to timestamp directly ,it didn't work out which would throw  error " invalid date, time or timestamp value :search table error:[ 6931 ] attribute value is not a date or wrong syntax."  That's why I tried another way , converting it to string then to timestamp, but neither of them succeeded , so I turned here for help.

the fields TST_TO has 14 digits , using ltrim is to get rid of the comma.

and also using Shreepad' method  will get numeric overflow error.  that's wired.

Best  regards,

Amon

[former_member242283]

Hi Shreepad,

  I tried your solution, still failed.

  I don't know why I can't use longdate() to directly converting timestamp. I will get search table error  [6931] attribute value is not a date or wrong syntax.

  It's when I can't directly converting from decimal to timestamp then I came up with the idea to firstly to string.

Best regards,

Amon

[Former Member]

Hi Amon,

Can you try

1. Creating a new table with one column of DECIMAL datatype.

2. Building an Attribute View on top of this.

3. Having a Calculated Column with TIMESTAMP datatype and the formula from my last post.

If this works then it could be an error with other Attributes involved in the Attribute View otherwise there is some error with what you're trying to do.

I will need more details of the attribute view that you're trying to build to debug that error.