CODEWITHSUNDEEP
sqloracle-databasedate
Sravanth Kothuri
Sravanth Kothuri

Reputation: 211

How to format date in DD-MMM-YYYY format eg 29-JAN-2015?

I want the date in DD-MMM-YYYY format eg 29-JAN-2015.

I have tried with:

SELECT TRIM(TO_DATE('29 Jan 2015'
                ,'DD MON YY')) FROM DUAL

I got result as: 29-JAN-15

But I am expecting: 29-JAN-2015 in date format not in char format

Upvotes: 6

Views: 87785

Answers (7)

Mahesh
Mahesh

Reputation: 27

In SQL Server the query

select CONVERT(nvarchar, GETDATE(), 106) as [Converted Date]

returns:

29 Jan 2015

Upvotes: -1

Sravanth Kothuri
Sravanth Kothuri

Reputation: 211

Thanks for answers. I got the solution. First we need to alter the session as below:

alter session set nls_date_format='DD-MON-YYYY';

then run the query: SELECT TRIM(TO_DATE('29 Jan 2015' ,'DD MON YYYY')) FROM DUAL Now I got result as:29-JAN-2015

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

What you are doing is take the string '29 Jan 2015' and make it a date using the format 'DD MON YY'. This should fail of course for '2015' not matching 'yy', but Oracle is lenient here.

Then you use TRIM on the date. But TRIM is for strings. What happens is that you get shown '29 Jan 15'. I am getting shown '29.01.15' instead of the usual '29.01.2015'. However the behavior: Don't use TRIM on dates, its behavior is nowhere specified as far as I am aware. Use TO_CHAR to format a date in output.

If you only select a date without TO_CHAR you get the date shown in some standard format, which can be '29 Jan 2015' or '29 Jan 15' or '29.01.2015' or '01/29/2015' depending on the app you are using and possibly some setting therin.

For completeness sake:

TO_DATE takes a string ('29 Jan 2015' in your case) and converts it to a date. If the string contains names, make sure that you specify the appropriate language setting:

TO_DATE('29 Jan 2015', 'dd mon yyyy', ''NLS_DATE_LANGUAGE=AMERICAN')

To get a formatted string from a date, use TO_CHAR:

TO_CHAR(sysdate, 'dd MON yyyy', 'NLS_DATE_LANGUAGE=AMERICAN')

Usually you don't do that, however. You select a date as is and have your app (written in PHP, Java or whatever) care about how to display it appropriately to the user's computer's settings.

Upvotes: 1

Manu
Manu

Reputation: 1

Can you try:

SELECT TRIM(TO_DATE(SYSDATE ,'DD MON YYYY')) FROM DUAL

YY will only show 2 ciphers, instead of YYYY that will show 4.

Upvotes: -4

Jay Vomex
Jay Vomex

Reputation: 1

Use CONVERT(VARCHAR(11),GETDATE(),106)

See detailed explanation here: http://www.w3schools.com/sql/func_convert.asp

Upvotes: -3

gnuchu
gnuchu

Reputation: 1496

Manu is correct. Oracle publish a full list of date format specifiers.

http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#CDEHIFJA

Upvotes: -2

rbm
rbm

Reputation: 3253

Im assuming Oracle DB:

select to_char(SYSDATE, 'dd-Mon-yyyy') from dual

Returns

29-Jan-2015

Upvotes: 9

Related Questions