Skip to Content

Date Conversion Error in HANA

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.

Add comment
10|10000 characters needed 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') ...
  • Get RSS Feed

5 Answers

  • Best Answer
    Nov 09, 2017 at 08:11 AM

    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)
    Add comment
    10|10000 characters needed characters exceeded

  • Nov 07, 2017 at 02:53 PM

    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)
    Add comment
    10|10000 characters needed characters exceeded

  • Nov 07, 2017 at 02:56 PM

    One more, this is also working:

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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 09, 2017 at 07:34 AM

    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


    Add comment
    10|10000 characters needed characters exceeded

  • Oct 24, 2018 at 06:34 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded