Home » SQL & PL/SQL » SQL & PL/SQL » date format (SQL Developer )
date format [message #678358] Wed, 27 November 2019 11:53 Go to next message
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.

Re: date format [message #678360 is a reply to message #678358] Wed, 27 November 2019 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
EdStevens
Messages: 1248
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
Re: date format [message #678373 is a reply to message #678358] Thu, 28 November 2019 01:27 Go to previous messageGo to next message
John Watson
Messages: 8344
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>
Re: date format [message #678389 is a reply to message #678373] Fri, 29 November 2019 07:39 Go to previous messageGo to next message
Bill B
Messages: 1969
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;
Re: date format [message #678393 is a reply to message #678389] Fri, 29 November 2019 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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.
Re: date format [message #678400 is a reply to message #678393] Sat, 30 November 2019 08:05 Go to previous message
EdStevens
Messages: 1248
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
Previous Topic: how to make table query in oracle (4 merged)
Next Topic: Finding Date Pattern using LEAD
Goto Forum:
  


Current Time: Sun Sep 20 06:38:58 CDT 2020