Skip to main content

[Migrated content. Thread originally posted on 15 March 2012]

Hi, i want to select just the date element of a timestamp columm. Does anyone know
what the common syntax / odbc escape sequence is (if there is any) that would enable me to use a single statement
that would work for both Sqlserver & Oracle without having to resort to the type of statement shown below ? Does anyone know if there is a webpage/document anywhere that lists techniques for making embedded sql more database agnostic ?:-

IF SQLSERVER
EXEC SQL
SELECT CONVERT(CHAR(8),NAMEDATE,112) INTO :H-DATETIME
FROM NCHARDATA
WHERE NAMEVAL = :H0286-NAME
END-EXEC
ELSE
EXEC SQL
SELECT TO_CHAR(NAMEDATE,'YYYYMMDD') INTO :H-DATETIME
FROM NCHARDATA
WHERE NAMEVAL = :H0286-NAME
END-EXEC
END-IF

Any advice would be much appreciated, thanks (i'm using Netexpress 5.104.0083)

[Migrated content. Thread originally posted on 15 March 2012]

Hi, i want to select just the date element of a timestamp columm. Does anyone know
what the common syntax / odbc escape sequence is (if there is any) that would enable me to use a single statement
that would work for both Sqlserver & Oracle without having to resort to the type of statement shown below ? Does anyone know if there is a webpage/document anywhere that lists techniques for making embedded sql more database agnostic ?:-

IF SQLSERVER
EXEC SQL
SELECT CONVERT(CHAR(8),NAMEDATE,112) INTO :H-DATETIME
FROM NCHARDATA
WHERE NAMEVAL = :H0286-NAME
END-EXEC
ELSE
EXEC SQL
SELECT TO_CHAR(NAMEDATE,'YYYYMMDD') INTO :H-DATETIME
FROM NCHARDATA
WHERE NAMEVAL = :H0286-NAME
END-EXEC
END-IF

Any advice would be much appreciated, thanks (i'm using Netexpress 5.104.0083)
If you are using OpenESQL then you should be able to define the host variable for the timestamp field as:

SQL TYPE IS TIMESTAMP.

and it should behave the same in both SQL Server and Oracle using ODBC.

That is the timestamp will be returned in the same format on both environments:

which would be equal to PIC X(29) in the format: yyyy-mm-dd hh:mm:ss.ffffff

You could then use standard COBOL reference modification to retrieve the date part using:

move date-field(1:10) to ...

or something similar to this.
Terms & ConditionsAccessibility statement