Date Conversion Error in HANA

[jyoti_senapati]

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.

[pfefferf]

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') ...

[D039507 · Accepted solution]

Hi Jyoti,

I think you have to generate your time data again:

Best Regards

Thorsten

[jyoti_senapati · Accepted solution]

Hi Thorsten,

Is this process will recreate the data of M_TIME_DIMENSION table ?

Regards,

Jyoti

[D039507 · Accepted solution]

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

[former_member594385]

Hi Jyothi,

The TO_DATE() function returns the output based on the format we mention as a parameter.

as mentioned above DATE_SQL value is a standard format that displays as output but in the system it stores as per the TO_DATE conversion or YYYY-MM-DD format by default.

So, Please cross check once again and let me know if you still face the issue.

Thanks,

Jay.

[jyoti_senapati]

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

[D039507]

One more, this is also working:

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

[D039507]

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