equivalent of PIC S9 with lenght 16 in oracle

Question

what is the equivaltent of PIC S9 with lenght 16 in oracle? I want it for storing timestamp

Answer 1

Don't know about PIC S9 but Oracle has a timestamp datatype.

Answer 2

If you want to store a date or a datetime with precision to seconds then use the DATE data type. If you need subsecond granularity then use the TIMESTAMP data type.

It's considered a very bad practice to store dates as a numeric or character representation in oracle as it makes date validation and date operations more complex, and can lead to poor query optimisation.

Answer 3

Actually, when managing timestamps in pro-COBOL, i do the following :

77 H-DATETIME PIC X(19).

In the Oracle Point of view, i use a DateTime object

When i want to retrieve the value, i use the following :

TO_CHAR(A.TIMESTAMP, 'YYYY-MM-DD-HH24-MI-SS')

When i want to update the Oracle field, i use the following :

TO_DATE(A.TIMESTAMP, 'YYYY-MM-DD-HH24-MI-SS')

The format i use allows sorting and using "SEARCH / SEARCHE ALL".

Hope this helps.