Home > Enterprise >  Substitute interval 1 day to SQL server syntax
Substitute interval 1 day to SQL server syntax

Time:12-04

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

CodePudding user response:

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.

  • Related