I'm currently updating a lot of our queries to work again in our new ERP-Release, where we will be working with MS SQL Server, swapping away from our current Informix database. Many are just simple Date-Format changes but this one I am unable to translate.

It really only is the following line:

round((GETDATE() - max(l105.dataen))::interval second(9) to second::char(10)::int
/ 60 / 60, 3)

I simply can't grasp what the part starting at the colons (::) is doing or what function it is.

I hope someone maybe can identify it.

CodePudding user response：

In Informix, subtracting two DATETIME values results in an INTERVAL. The <value>::<type> notation is a shorthand for CAST(<value> AS <type>). Therefore, there are three consecutive casts:

1. ::interval second(9) to second
2. ::char(10)
3. ::int

By default, if you subtract two DATETIME YEAR TO SECOND values, you will get an INTERVAL DAY(n) TO SECOND value. The first cast converts that to INTERVAL SECOND(9) TO SECOND — a number of seconds; the second cast converts the result to CHAR(10) because there isn't a direct conversion from INTERVAL to numeric types; the third cast converts the string to an INT. That gives the integer number of seconds; it is divided by 60 twice (effectively, divided by 3600) to convert the seconds into hours.

The result is then rounded to 3 decimal places.

• So, the overall operation calculates the number of hours between two times.

The two times are the current time and the most recent value in the l105.dataen column (the MAX expression). Presumably, there is a GROUP BY clause somewhere in the SELECT statement that this is a part of.

You will likely need to use a 'time difference' function in MS SQL Server, and maybe the function allows you to control the presentation of the result as a number of hours and fractions of an hour.

Judging from DATEDIFF function, you will need to use something like:

DATEDIFF(hh, MAX(l105.dataen), GETDATE())

However, that returns an integer value for the difference in hours. You may prefer to get the time in seconds and divide by 3600 to get a fractional value:

DATEDIFF(ss, MAX(l105.dataen), GETDATE()) / 3600.0

^{No database server was consulted to ensure the veracity of the suggested translation.}