Oracle SQL Function return date format

Question

I have small problem on Oracle PL-SQL,

I created a function

that at the end looks like this :

 wynik := concat(miesiac,concat(dzien,rok));
 w_date := TO_DATE(wynik);
 return w_date;

It works fine but it returns date in format MM/DD/YYYY, and i would want to return in format of DD MONTH YYYY, how can i reach that without alter sesion on global settings?

I tried messing with:

w_date := TO_DATE(wynik,'DD MONTH YYYY');

But it gives me errors....

Thanks for your help.

possible duplicate of [Oracle Date format](http://stackoverflow.com/questions/17326469/oracle-date-format) — Noel, Nov 14 '13 at 14:25
The function does not return the date in any format at all -- it returns a DATE datatype, which is unambiguous. When you display it, it has to be converted to a human readable string, and that display format is configurable. — David Aldridge, Nov 14 '13 at 14:36

score 2 · Accepted Answer · answered Nov 14 '13 at 14:25

2

TO_DATE converts string to date and it is rendered to output using default date format definied for session. If you want to display it differently use TO_CHAR function or change default format.

Example TO_CHAR usage:

TO_CHAR(sysdate, 'DD MONTH YYYY')

To change default format:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD MONTH YYYY';

answered Nov 14 '13 at 14:25

Krzysztof

15,900
2
46
76

The problem is that the guiideline of this function is to return date variable, of course with TO_CHAR it works perfectly fine but i need to return as date, as shown on below code. `CREATE OR REPLACE FUNCTION Pesel_dataur(p_pesel IN CHAR) return date` So the only option for correct result with proper format is to Alter global settings? – plomien Nov 14 '13 at 14:31
So just return `w_date`. What is the problem? You can return date and relay on NLS_DATE_FORMAT or return string and provide custom format. I don't see another option. – Krzysztof Nov 14 '13 at 14:33
And there is no such thing as proper format for date. Whole date is stored internally despite NLS_DATE_FORMAT. NLS_DATE_FORMAT is only used to display DATE object in readable way when you do select. – Krzysztof Nov 14 '13 at 14:36

score 1 · Answer 2 · answered Nov 14 '13 at 14:28

Internally, the DATE is stored as a number, not as a string, so neither the string converted to the DATE, nor the format date model used (explicitly or implicitly, from session parameter) during the creation of the variable, has anything to do with the way a DATE variable is presented to the user.

To show a DATE variable as a string, the TO_CHAR function should be used, and the format mask used:

SELECT TO_CHAR(SYSDATE, 'DD MONTH YYYY') AS val FROM dual;

VAL                                                
----------------------------------------------------
14 LISTOPAD    2013

To suppress the extra blank spaces, use the fm modifier:

SELECT TO_CHAR(SYSDATE, 'DD fmMONTH YYYY') AS val FROM dual;

VAL                                                
----------------------------------------------------
14 LISTOPAD 2013

score 0 · Answer 3 · answered Nov 14 '13 at 15:26

0

w_date := TO_CHAR(wynik,'DD MONTH YYYY');

answered Nov 14 '13 at 15:26

mucio

7,014
1
21
33

Oracle SQL Function return date format

3 Answers3