Field name conversion into understandble format

[Former Member]

Hi team,

I was trying to collect the data from the view M_BACKUP_CATALOG.

20150315081801546000000 : SYS_START_TIME.

When i check the functions  i dont see a function to convert this field into date and time.

https://help.sap.com/saphelp_hanaone/helpdata/en/20/f1662775191014a6f7cead37a5937a/content.htm?frame...

[Former Member · Accepted solution]

hI,

are you looking for somethign like below ? that column is a timestamp type and below function can covert to date and time.

select TO_DATE(SYS_START_TIME ), TO_TIME(SYS_START_TIME ) from M_BACKUP_CATALOG

-Siva

[Former Member · Accepted solution]

Hi Siva,

I did try the TO_TIME function it didnt work. Please check it doesnt work.

We have to use something else i think.

[Former Member · Accepted solution]

it will work for sure as the datatype conversion is possible for timestamp to time and vice versa.

Data Types - SAP HANA SQL and System Views Reference - SAP Library

Could you please share the screenshot like definition , errors and your SQL ?

Siva

[Former Member · Accepted solution]

This is the error what i was getting.

xception CX_DBA_ADBC in class CL_HDB_SQL_EXECUTOR method EXEC_QUERY_DYN line 123

  Kernel Error ID:  

  WP ID: 29 

  WP PID: 14489

  Application Server: va2lpsap065

  SYSID: UPA

  SY-SUBRC: 0 

  SQL statement: select TO_DATE(SYS_START_TIME ), TO_TIME(SYS_START_TIME ) from M_BACKUP_CATALOG

  Database: DEFAULT

caused by

Exception CX_SQL_EXCEPTION in class CL_SQL_RESULT_SET 

  Kernel Error ID:  

  DB Error: Yes

  SQL Code: 10412-

  SQL Message: Illegal TIME value for parameter/column (2)

  DB Object Exists: No

  Duplicated Key: No

  Internal Error: 1 

  Invalid Cursor: No

  Unknown Connection: No

[Former Member · Accepted solution]

I just checked in my side and it works .What HANA revision you are in ? Pls also share the view definition and data preview (value) of your view for that column SYS_START_TIME ?

[Former Member · Accepted solution]

My HANA revision is 82.

View definition is below.

ROW TABLE "SYS"."M_BACKUP_CATALOG" ( "ENTRY_ID",

"ENTRY_TYPE_NAME",

"BACKUP_ID",

"SYS_START_TIME",

"UTC_START_TIME",

"SYS_END_TIME",

"UTC_END_TIME",

"STATE_NAME",

"COMMENT",

"MESSAGE" ) AS ( SELECT

CAST(T0.ENTRY_TYPE_NAME AS VARCHAR(64)) ENTRY_TYPE_NAME,

CAST(T0.COMMENT AS VARCHAR(256)) COMMENT,

CAST(T0.MESSAGE AS VARCHAR(512)) MESSAGE

FROM SYS.M_DEV_BACKUP_CATALOG_DATA_ T0

WHERE 1 = (SELECT

FROM SYS.HAS_NEEDED_SYSTEM_PRIV_INCL_SYS_STAT)

OR HASSYSTEMPRIVILEGE (CURRENT_USER,

'BACKUP ADMIN') = 1

OR HASSYSTEMPRIVILEGE (CURRENT_USER,

'BACKUP OPERATOR') = 1

UNION ALL SELECT

CAST('log backup' AS VARCHAR(64)) ENTRY_TYPE_NAME,

CAST('' AS VARCHAR(256)) COMMENT,

CAST(T0.MESSAGE AS VARCHAR(512)) MESSAGE

FROM SYS.M_DEV_BACKUP_CATALOG_LOG_ T0

WHERE 1 = (SELECT

FROM SYS.HAS_NEEDED_SYSTEM_PRIV_INCL_SYS_STAT)

OR HASSYSTEMPRIVILEGE (CURRENT_USER,

'BACKUP ADMIN') = 1

OR HASSYSTEMPRIVILEGE (CURRENT_USER,

'BACKUP OPERATOR') = 1 )

BY ENTRY_ID WITH READ ONLY

[Former Member · Accepted solution]

actually i was expecting the data type for that column SYS_START_TIME . but i believe it has to be timestamp. Also please show me the value of that column.. it looks like the value are not good or invalid value for that column in the table...

[Former Member · Accepted solution]

Hi

You can use the below code to get the date out of it.

select concat(substr('20150315081801546000000',1,4),concat('-',concat(substr('20150315081801546000000',5,2),concat('-',substr('20150315081801546000000',7,2))))) from dummy

Note: Replace the value with column name.

Regards,

Venkat N

[Former Member · Accepted solution]

Siva, i was telling you its not a standard field it has both date and timestamp in the same field. If you check my first post.

[Former Member · Accepted solution]

Hi Venkat,

But i wanted something when i do it for a column all the values are changed. Individually changing each and every value would be cumbersome right.

[Former Member · Accepted solution]

Hi venkat,

Can you please help me understand as to how to use the concat and substr for the whole column rather than a specific value?

[Former Member · Accepted solution]

let me try to help you..It really confuse me when you say it is not a standard field . The column SYS_START_TIME is standard field having TIMESTAMP as data type and this would help in getting server local time of your backlog catalog.

try this way , may fix your problem : Windows - > Preference ->Runtime -> Result -> Format values .

Now run simple sql and check your column value after changing this format value , if format for that column got changed then as next step use the function TO_TIME to see expected result

Thanks

Siva

[Former Member · Accepted solution]

Hey siva thanks a ton for the info. Sorry about the delay.

I did check that option but i dont see that option in studio. My studio version is 82.

Hey the problem is that column value contains date and time in the same field.