Equivalent of PIC S9 with length 16 in Oracle

What is the equivalent of PIC S9 with length 16 in Oracle? I want it for sto开发者_Python百科ring timestamp.

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.

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

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.