OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » date format (SQL Developer )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

date format [message #678358]

Wed, 27 November 2019 11:53

kabina
Messages: 8
Registered: November 2019

Junior Member

I am new to Oracle SQL , I know SQL how it works. Syntax are bit different than SQL server . I build reports in companies. I need to pull some data from oracle database.

I want know , I have field POCREATED_DATE datatype date

when I use select query to get this date it gives me 01-FEB-17 but I want in this format 02/01/2016 2:21:00 PM , I want my data type remain same.

Thanks in advance.

Report message to a moderator

Re: date format [message #678360 is a reply to message #678358]

Wed, 27 November 2019 12:15

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

You need to use TO_CHAR with the appropriate date format to display it as you want ('MM/DD/YYYY HH:MI:SS PM' for your example).

[Edit: SS missing in date format]

[Updated on: Wed, 27 November 2019 14:50]

Report message to a moderator

Re: date format [message #678362 is a reply to message #678360]

Wed, 27 November 2019 12:58

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

To add to and expand on Michel's post, See
https://edstevensdba.wordpress.com/category/dealing-with-dates/

and

https://community.oracle.com/docs/DOC-991195

Report message to a moderator

Re: date format [message #678373 is a reply to message #678358]

Thu, 28 November 2019 01:27

John Watson
Messages: 8977
Registered: January 2010
Location: Global Village

Senior Member

Perhaps Oracle is a bit more versatile than SQL Server? For example (and please note the use of code tags),

orclz> select
  2  sysdate session_default,
  3  to_char(sysdate,'dd-MON-yyyy') typical_us,
  4  to_char(sysdate,'dd Month year','nls_date_language=Czech') more_variations
  5  from dual;

SESSION_DEFAULT     TYPICAL_US           MORE_VARIATIONS
------------------- -------------------- ------------------------------------------------------------------------------
2019-11-28:07:27:19 28-NOV-2019          28 Listopad twenty nineteen

orclz>

Report message to a moderator

Re: date format [message #678389 is a reply to message #678373]

Fri, 29 November 2019 07:39

Bill B
Messages: 1971
Registered: December 2004

Senior Member

The second argument is the format mask. All you need to do if you want it to use the default for your workstation simply use

select to_char(POCREATED_DATE,'MM/DD/YYYY HH:MI:SS AM') FROM TABLE_NAME;

Report message to a moderator

Re: date format [message #678393 is a reply to message #678389]

Fri, 29 November 2019 09:13

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

We tend to say that, but to be honest - if you're talking about an application querying dates it's usually best to set the session nls_date_format, so every can get their preferred format and you don't have to write to_char all the time.

Report message to a moderator

Re: date format [message #678400 is a reply to message #678393]

Sat, 30 November 2019 08:05

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

cookiemonster wrote on Fri, 29 November 2019 09:13

We tend to say that, but to be honest - if you're talking about an application querying dates it's usually best to set the session nls_date_format, so every can get their preferred format and you don't have to write to_char all the time.

I think that in this case "best", like "beauty", is in the eye of the beholder. While writing the functions at the statement level may seem cumbersom when writing the code, I'd argue that it increases the clarity when someone else has to maintain the code. They can see right there at the statement what is going on without having to look for where and how the NLS settings are set. Smile

Report message to a moderator

Previous Topic:	how to make table query in oracle (4 merged)
Next Topic:	Finding Date Pattern using LEAD

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 28 21:16:41 CDT 2025