CODEWITHSUNDEEP
oracle-databaseoracle11gplsqldeveloper
Faizan Mansoor
Faizan Mansoor

Reputation: 1

How to change date format to 'dd-Mon-yy' in Oracle

I'm trying to get a specific date format 'DD-Mon-YY'

My table has a date field (birth_dt) which has a default format of 'dd/mm/yyyy'

I do not want the result to be a text (i.e. do not want to use to_char()) as the result needs to be exported to an Excel spreadsheet and some sorting needs to be done by the business based on the date field.

I tried to use : 

SELECT to_date(to_char(t.birth_dt,'dd-Mon-yy'), 'dd-Mon-yy') FROM  TABLE t

but it still gives the same date format as described earlier (dd/mm/yyyy).

Any help would be much appreciated.

Thank you.

Upvotes: 0

Views: 7269

Answers (3)

Avrajit Roy
Avrajit Roy

Reputation: 3303

The answer you are looking can be done by playing a bit with NLS_SESSION parameters. Hope illustration helps.

Note : Thi settings will only remian for the current session. After closing the session, settings will also get reverted.

SELECT sysdate+ level FROM dual CONNECT BY LEVEL < 10;

--Now check the NLS_DATE_FORMAT in NLS_SESSION_PARAMEERS table

SELECT * FROM nls_session_parameters;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YY';_

--Now check the NLS_DATE_FORMAT in NLS_SESSION_PARAMEERS table

SELECT sysdate+ level FROM dual CONNECT BY LEVEL < 10;

Upvotes: 0

user5683823
user5683823

Reputation:

You seem confused about the DATE data type. DATE does not have a "format" (if you really care to see how dates are stored internally in Oracle, you can, with the DUMP() function - you will see that the internal format has nothing to do with format models). The 'dd/mm/yyyy' and all the variations are ONLY for the representation of dates AS STRINGS, and not for anything else.

Export the dates in DATE data type to Excel. Whatever formatting you need in Excel you can do there. Whatever ordering you need to do, do that by date - no "format model" is needed for correct ordering.

Regarding your confusion: When you SELECT a date (but not when you export it!) it must be converted to a string, because you can only "see" characters. Oracle converts the date based on your current NLS_DATE_FORMAT parameter - but this parameter is not used (and is irrelevant) either when you order by the date field or when you export to Excel, as long as you export in DATE data type (as you should).

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

to_date(to_char(t.birth_dt,'dd-Mon-yy'), 'dd-Mon-yy') doesnt make any sense.

You are converting date to string and then back to date.

Dates in Oracle don't have any format. Format are applied on them. Date is displayed in a format in your tool based on your NLS parameters. You can set them in your tool's NLS settings.

Upvotes: 0

Related Questions