CODEWITHSUNDEEP
sqloracle-database
Praveen Verma
Praveen Verma

Reputation: 41

Converting TIMESTAMP column in 12 HR format into Date column with 24 HR format

In a table, I have a TIMESTAMP column in 12 HR format which I want to convert into Date column with 24 HR format.

Insert_Date

3-JAN-19 09.50.25.000000 AM 

23-JAN-19 08.54.37.000000 PM

I tried following-

to_char(Insert_Date,'YYYY-MM-DD HH:MI:SS')

Expected Result is -

3-JAN-19 09.50.25

23-JAN-19 20.54.37

Upvotes: 0

Views: 769

Answers (2)

MT0
MT0

Reputation: 167822

Dates (and timestamps) do not have a format - they are represented internally by 7 or 8 bytes for a date or 20 bytes for a timestamp. The only time they are formatted is either when the user interface you are using implicitly converts then so it can display something meaningful to the user (i.e. when displaying a SELECT statement) or when the user explicitly converts it using TO_CHAR.

Given this, you do not need to change any format; just CAST to a different data type:

SELECT CAST( your_timestamp_column AS DATE ) FROM your_table

Why does it display TIMESTAMPs using 12 hour clock and DATEs using 24 hour clock?

Because that's what your user interface has its defaults set to.

For SQL/Plus & SQL Developer the defaults are in the user's NLS session parameters:

SELECT parameter, value
FROM   NLS_SESSION_PARAMETERS
WHERE  parameter IN ( 'NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT' );

You should see something like:

PARAMETER            VALUE
-------------------- ---------------------------
NLS_DATE_FORMAT      DD-MON-RR HH24:MI:SS
NLS_TIMESTAMP_FORMAT DD-MON-RR HH12:MI:SS.FF6 AM

You can alter them using:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS.FF9';

If, for example, you wanted them both in ISO8601 format.

Upvotes: 2

Ted at ORCL.Pro
Ted at ORCL.Pro

Reputation: 1612

CAST(timestamp_expression AS DATE);

e.g. SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;

For presentation:

to_char(Insert_Date,'DD-MON-RR HH24:MI:SS')

Upvotes: 0

Related Questions