Substitute interval 1 day to SQL server syntax

user_10595871 0
Viewed 174

I have this datetime, working in Hana:

CAST((B."ZFBDT" + B."ZBD1T" * INTERVAL '1 day') AS DATE) + CAST('23:59:59' AS TIME)

B."ZFBDT" = '2020-10-30'
B."ZBD1T" = 67
RES = 2021-01-05 23:59:59

The problem is that I need to convert it into SQL Server, and I now that INTERVAL is not supported. Actually I don't even understand the syntax properly: why they are multipling for INTERVAL 1 day?

I could unserstand adding or subtracting, but why multipling? Anyway, how can I make this work in SQL Server?

Thanks

hana sql-server sql
1 Answers

On SQL Server you may try the following:

DATEADD(SECOND, -1, DATEADD(DAY, B.ZBD1T + 1 , CAST(B.ZFBDT AS datetime)))

You may phrase your problem as wanting to add B.ZBD1T days to the input date B.ZFBDT, and then place the resulting timestamp at 23:59:59, i.e. one second before midnight. We can achieve that by adding B.ZBD1T plus one days, then subtracting one second to get the 23:59:59 time component.

user_18632290
Related