Add milliseconds to date in SQL, not seconds

Question

I'm writing a procedure on the SAP HANA database (using SPS 07).

In this procedure I save the current timestamp in a variable:

vMyTimestamp := CURRENT_UTCTIMESTAMP;

I know from HANA's SQL and System Views Reference that there are currently 5 functions available to add an amount of time to a date:

ADD_YEARS
ADD_MONTHS
ADD_DAYS
ADD_WORKDAYS
ADD_SECONDS

However, what I need is to add only a certain amount of milliseconds to a date, so for example if the current timestamp is something like 2014-04-10 09:11:45.0 I want to get 2014-04-10 09:11:45.1

Is there a way to achieve this?

Thanks in advance.

Did you try `ADD_SECONDS` with value 0.001? – rs. Apr 10 '14 at 13:20 — rs., Apr 10 '14 at 13:20

score 3 · Accepted Answer · answered Apr 10 '14 at 15:06

3

You should be able to add the seconds as a fractional value (e.g., 0.1 seconds = 100 milliseconds). The following, adds 100 milliseconds to the given timestamp:

SELECT ADD_SECONDS (TO_TIMESTAMP('2014-04-10 09:11:45'), 0.1) FROM DUMMY

answered Apr 10 '14 at 15:06

Mark Wilkins

40,729
5
57
110

Didn't think about that. Works like charm - thank you! And also thanks to @rs. – hoffman Apr 14 '14 at 18:11

Add milliseconds to date in SQL, not seconds

1 Answers1