Skip to Content
0

Date Conversion Error in HANA

Nov 07, 2017 at 12:36 PM

146

avatar image

Hi Experts,

I am trying to show 1900-01-01 as in Data format in SQL Console by using below SQL Statement.

SELECT TO_DATE('1900-01-01') as DATE FROM DUMMY;

But in the output is shows 31 Dec,1899.

PFB Screenshot.

Please help out to resolve this issue.

10 |10000 characters needed characters left characters exceeded

What HANA revision you are using? I tested it with an 1.0 SPS12 system and it works.

Have you tried to set the format, just to check if that changes the behavior? E.g.

SELECT TO_DATE('1900-01-01', 'YYYY-MM-DD') ...
0
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
avatar image
Former Member
Nov 09, 2017 at 08:11 AM
0

Hi Jyoti,

I think you have to generate your time data again:

Best Regards

Thorsten


timedata1.jpg (23.7 kB)
timedata2.jpg (28.9 kB)
Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Thorsten,

Is this process will recreate the data of M_TIME_DIMENSION table ?

Regards,

Jyoti

0
Former Member

Hi Jyoti,

yes, I think System will delete existing and load new one. But you have to use the same period of time as is in table for now, e.g. 2018 - 2020.

Best Regards

Thorsten

0
avatar image
Former Member
Nov 07, 2017 at 02:53 PM
0

Hi,

for me, this is working fine. For 1900-01-01 and also 19000101:

I'm using Hana Studio Version: 2.3.25 and hana1sp12.

best regards

thorsten


hana1.jpg (19.4 kB)
hana2.jpg (16.9 kB)
Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member
Nov 07, 2017 at 02:56 PM
0

One more, this is also working:

SELECT TO_DATE('19000101', 'YYYYMMDD') "DATE" FROM DUMMY;

Share
10 |10000 characters needed characters left characters exceeded
Jyoti Senapati Nov 09, 2017 at 07:34 AM
0

Hi Thorsten,

Thanks for your reply. Actually I have searched in the DATE_SQL field in M_TIME_DIMENSION table and found that the entry is wrong in that table. PFB

Is any other work around to handle this issue?

Regards,

Jyoti


Share
10 |10000 characters needed characters left characters exceeded