CODEWITHSUNDEEP
oracle-databasedatetime-formatoracle12c
Oto Shavadze
Oto Shavadze

Reputation: 42793

Little bit misunderstanding with datetime formats

SELECT TO_TIMESTAMP('2016-10-01 01:00:01', 'YYYY-MM-DD HH24:MI:SS') from dual;

Gives : 2016-10-01 01:00:01

Here year and month parts are understood for me, but Documentation says about other formats:

"DD" - Day of month (1-31)

"HH24" - Hour of day (0-23)

"MI" - Minute (0-59)

"SS" - Second (0-59)

So, why for example "DD" format returns 01 and not just: 1 ?

For example "MM" format, in same doc described as:

MM - Month (01-12; January = 01) and here everything clearly: January = 01 .

Also, little bit confusing is time parts (for me at least) , because in documentation, range for all begins with 0 but returns result as 00

I've expected from above query, result like this:

2016-10-1 1:0:1

What I missed and don't understood correctly?

Upvotes: 1

Views: 49

Answers (1)

user5683823
user5683823

Reputation:

First you are passing in two strings: the actual datetime (or timestamp) you want to convert to timestamp datatype, and the format model. Then you call to_timestamp(), and the result is a timestamp. Then you DISPLAY this on screen (through SQLPlus or SQL Developer or whatever). Only strings can be displayed on screen - not numbers, not timestamps, etc. So: somewhere, somehow, your timestamp is converted back to a string.

You either do that explicitly, by calling to_char(.....) around your timestamp, or if you don't, the interface application (SQLPlus, SQL Developer, ...) uses your session's NLS_TIMESTAMP_FORMAT parameter. In that parameter, you can force whatever format model you want. In particular, you can suppress leading zeros with the FM format model modifier.

Without changing your NLS_TIMESTAMP_FORMAT you can see the same effect by using to_char() in the query. Try this:

select to_char(to_date('01-Feb-12', 'dd-Mon-yy'), 'FMdd/mm/yyyy hh24:mi:ss') from dual;

Output: 1/2/2012 0:0:0

Upvotes: 1

Related Questions