0

When I run

SELECT TO_TIMESTAMP('8-JUN-20 10.30.05.024000000 PM','DD-Mon-RR HH.MI.SS.FF AM') FROM dual;

return this:

08/06/20 10:30:05.024000000 PM

But I want is that return this:

08-JUN-20 10.30.05.024000000 PM
NaguiHW
  • 43
  • 6
  • 2
    it already returns what you want : *db<>fiddle [here](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=52a2206c47d375d5828798d2a389cdc2)* – eshirvana Jun 11 '21 at 14:18
  • Great! But why in my local machine in Oracle SQL Developer doesn't show like that? It doesn't matter? – NaguiHW Jun 11 '21 at 14:26
  • 2
    It doesn't matter. If you insist, alter session and change timestamp format mask. – Littlefoot Jun 11 '21 at 14:29
  • 4
    Do you want a timestamp (which your client can format - check your `NLS_TIMESTAMP_FORMAT` setting in preferences); or a string representation of a timestamp? As you already have a string to pass into `to_timestamp()`, presumably the former. – Alex Poole Jun 11 '21 at 14:29
  • 2
    maybe its your client changing the date format , look into your client settings – eshirvana Jun 11 '21 at 14:29
  • 1
    [Related answer](https://stackoverflow.com/a/51269015/266304); I'm sure there's a proper duplicate question out there somewhere... – Alex Poole Jun 11 '21 at 14:53
  • 2
    A TIMESTAMP and a DATE are internal binary formats. Everything about them that is displayed on your screen is _necessarily_ a character representation of the timestamp or date -- the result of either an implicit or explicit TO_CHAR() function applied to the TIMESTAMP or DATE. If you don't do an explicit TO_CHAR() the implicit one used by oracle will format the output according to the session NLS_TIMESTAMP_FORMAT or NLS_DATE_FORMAT settings. These settings are under the control of your client session. – EdStevens Jun 11 '21 at 15:38
  • https://stackoverflow.com/questions/67939144/convert-timestamp-in-oracle-sql – EdStevens Jun 11 '21 at 15:40

1 Answers1

0

TO_TIMESTAMP returns a TIMESTAMP datatype. Are you looking to show a date/time variable with a specific date format? If so, use TO_CHAR with desired date/time format, i.e:

Select to_char(sysdate,'DD-MON-RR HH.MI.SS.FF AM') from dual;
Raf
  • 131
  • 1
  • 6