SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-30

CONVERTTIMESTAMP Function

The CONVERTTIMESTAMP function converts a Julian timestamp to a value with data

type TIMESTAMP.

CONVERTTIMESTAMP is an SQL/MX extension.

julian-timestamp

is an expression that evaluates to a Julian timestamp, which is a LARGEINT value.

Considerations for CONVERTTIMESTAMP

Relationship to the JULIANTIMESTAMP Function

The operand of CONVERTTIMESTAMP is a Julian timestamp, and the function result

is a value of data type TIMESTAMP. The operand of the JULIANSTAMP function is a

value of data type TIMESTAMP, and the function result is a Julian timestamp. That is,

the two functions have an inverse relationship to one another.

Use of CONVERTTIMESTAMP

You can use the inverse relationship between the JULIANTIMESTAMP and

CONVERTTIMESTAMP functions to insert Julian timestamp columns into your

database and display these column values in a TIMESTAMP format.

Examples of CONVERTTIMESTAMP

•

Suppose that the EMPLOYEE table includes a column, named HIRE_DATE, which

contains the hire date of each employee as a Julian timestamp. Convert the Julian

timestamp into a TIMESTAMP value:

SELECT CONVERTTIMESTAMP (hire_date)

FROM persnl.employee;

•

This example illustrates the inverse relationship between JULIANTIMESTAMP and

CONVERTTIMESTAMP.

SELECT CONVERTTIMESTAMP (JULIANTIMESTAMP (ship_timestamp))

FROM persnl.project;

If, for example, the value of SHIP_TIMESTAMP is 1998-04-03 21:05:36.143000,

the result of CONVERTTIMESTAMP(JULIANTIMESTAMP(ship_timestamp)) is

the same value, 1998-04-03 21:05:36.143000.

CONVERTTIMESTAMP (julian-timestamp)