Unusual results from TO_SECONDDATE() function

[former_member314792]

I'm using the TO_SECONDDATE() function in HANA SQLscript to combine a date field in format YYYYMMDD with a time field in HH24MISS.  These are separate fields in a table.  So, my SQL is TO_SECONDDATE( BEWIDT || BEWIZT ).  This works fine *unless* the date is exactly '99991231' and the time is exactly '240000' (both are allowable maximum values).  All the other dates/times convert properly if I add a where BEWIDT <> '99991231' clause to the query!  No format is given.

The error that's being returned is:

"Could not execute 'select to_seconddate(BWIDT || BWIZT) dtm from SAPYP0.NBEW' in 131 ms 565 µs .

[303]: invalid DATE, TIME or TIMESTAMP value: search table error:  [6931] attribute value is not a date or wrong syntax;seconddate [here]seconddate(string sqladd(string "BWIDT", string "BWIZT")),(BWIDT = '99991231'[string], BWIZT = '240000'[string]); checkNull false"

Seems like a boundary condition?

Thanks in Advance for enlightenment,

  Donn

[muthuram_shanmugavel2 · Accepted solution]

Hi Donn,

in HANA, Timestamp won't accept the format 24:00:00 (HH:MM:SS).

It will accept 00:00:00

Example:

I am adding one second from '2016-07-01 23:59:59',

The result will be '2016-07-02 00:00:00.0'

Calculating '99991231' || '235959' is not making any issues.

So the Maximum Timestamp Value in HANA Database is

'9999-12-31 23:59:59'

(Yes, It is Boundary Condition)

Regards,

Muthuram

[former_member314792 · Accepted solution]

Thanks, Muthuram,

It is indeed the situation; setting the time to '000000' (and leaving the corresponding date '99991231') allows the function to work properly.

However, as you mentioned there is a boundary issue with the conversion functions

Works:   SELECT TO_SECONDDATE ('99991231' || '000000') from DUMMY

Fails:      SELECT TO_SECONDDATE ('99991231' || '240000') from DUMMY

Both work: SELECT TO_TIME ('240000'), TO_TIME ('000000') from DUMMY

Fails:     SELECT TO_DATE ('99991231240000') from DUMMY

Works:   SELECT TO_TIME ('99991231240000') from DUMMY

In short, if 24:00:00 is accepted (and documented) as a valid time, then all the conversion functions should allow that maximum.  Do you think this is something I should open an incident on?

TIA,

  Donn

[muthuram_shanmugavel2 · Accepted solution]

Hi Donn,

If We execute

SELECT TO_DATE ('20160708240000') from DUMMY --> 8th July 2016

It is giving result as next day of 8th July.

8th July 24:00:00 = 9th July 00:00:00

When we execute, SELECT TO_DATE ('99991231240000') from DUMMY

It is trying to calculate next day of 9999-12-31. So It is giving error.

---------------------------------------------------------------------------------------------------------------------------------

If We execute,

SELECT TO_TIME ('99991231240000') from DUMMY

It is giving result as 00:00:00

24:00:00 --> calculated as 00:00:00. So It got executed successfully.

For 9999-12-31 24:00:00, TO_TIME only considers time, So it executed successfully.

For 9999-12-31 24:00:00, TO_DATE considers date, So it got failed.

HANA DB accepts 24:00:00 Format Except 9999-12-31,

It takes 24:00:00 as 00:00:00 of Next Day.

We are getting error for 9999-12-31 24:00:00, Because It is calculating next day of 9999-12-31.

So we are getting error.

Example:

24:00:00 Format will work for 2016-07-08 23:59:59

After 1 second,

Format either 2016-07-08 24:00:00 (or) 2016-07-09 00:00:00.

HANA displays the data in second format.

Regards,

Muthuram

[former_member314792 · Accepted solution]

Yes, I see what it's doing.   A  combination of the two conversions.   I still feel that 24:00:00 should be accepted as a valid time for a 99991231 date (it's not like anyone *expects* it to actually roll over)...

Thanks,

Donn