Home » SQL & PL/SQL » SQL & PL/SQL » Changing the date format on a specific field
Changing the date format on a specific field [message #666164] Wed, 18 October 2017 00:49 Go to next message
amandasmile
Messages: 1
Registered: October 2017
Junior Member
Hi !

I just want to know what would be the SQL command to change a date format to 'mm/dd/yyy' in SQL. I can't find a way to modify it in the EDIT TABLE menu.


Thanks

Report message to a moderator

Re: Changing the date format on a specific field [message #666165 is a reply to message #666164] Wed, 18 October 2017 01:44 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

A column of data type DATE is always stored in the same internal format, over which you have no control. You format it when you project the column in whatever way you want. For example, format SYSDATE in different ways:
orclx>
orclx> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss'),to_char(sysdate,'day month year') from dual;

TO_CHAR(SYSDATE,'YY TO_CHAR(SYSDATE,'DAYMONTHYEAR')
------------------- ------------------------------------------------------------
2017-10-18:07:41:50 wednesday october   twenty seventeen

orclx>

Lastly, please don't say "field" when you mean "column".

Report message to a moderator

Re: Changing the date format on a specific field [message #666166 is a reply to message #666165] Wed, 18 October 2017 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Note that the names depend on your NLS_DATE_LANGUAGE (or NLS_LANGUAGE) setting for the DAY, DY, MONTH, MON format elements but not for the YEAR format element:
SQL> select to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss'),to_char(sysdate,'day month year') from dual;
TO_CHAR(SYSDATE,'YY TO_CHAR(SYSDATE,'DAYMONTHYEAR')
------------------- -------------------------------------------------------------
2017-10-18:09:50:50 mercredi octobre   twenty seventeen

Report message to a moderator

Re: Changing the date format on a specific field [message #666167 is a reply to message #666166] Wed, 18 October 2017 03:58 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Yes. I often forget the importance of language settings, even though I remind people of them continuously. The numeric formats can cause horrific confusion:
orclx> alter session set nls_territory='France';

Session altered.

orclx> select 1234/1000 from dual;

 1234/1000
----------
     1,234

orclx> alter session set nls_territory='United Kingdom';

Session altered.

orclx> select 1234/1000 from dual;

 1234/1000
----------
     1.234

orclx>
So, amandasmile, we really should explicitly specify NLS for every type conversion:
orclx> select to_char(sysdate,'day month year','nls_date_language=Hungarian') from dual;

TO_CHAR(SYSDATE,'DAYMONTHYEAR','NLS_DATE_LANGUAGE=HUNGARIAN')
--------------------------------------------------------------------------------------------------------------
szerda    okt¾ber    twenty seventeen

orclx>

Report message to a moderator

Re: Changing the date format on a specific field [message #666168 is a reply to message #666166] Wed, 18 October 2017 04:02 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's got to be an over-sight on oracles part.

Report message to a moderator

Re: Changing the date format on a specific field [message #666170 is a reply to message #666168] Wed, 18 October 2017 04:23 Go to previous messageGo to next message
Alien
Messages: 291
Registered: June 1999
Senior Member
Hi,

if it is an oversight, then it is a documented one Smile
From the globalization guide
https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF51080


The language in which these values are returned is specified either explicitly with the initialization parameter NLS_DATE_LANGUAGE or implicitly with the initialization parameter NLS_LANGUAGE. The values returned by the YEAR and SYEAR datetime format elements are always in English.

Regards,

Arian

Report message to a moderator

Re: Changing the date format on a specific field [message #666178 is a reply to message #666164] Wed, 18 October 2017 09:40 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's nice, but - what the heck "EDIT TABLE menu"?

amandasmile
... to modify it in the EDIT TABLE menu

Report message to a moderator

Re: Changing the date format on a specific field [message #666181 is a reply to message #666164] Wed, 18 October 2017 09:59 Go to previous messageGo to next message
Alien
Messages: 291
Registered: June 1999
Senior Member
Read the first response to your question. There is no date format on a table.

Regards

Arian

Report message to a moderator

Re: Changing the date format on a specific field [message #666183 is a reply to message #666181] Wed, 18 October 2017 12:05 Go to previous messageGo to next message
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your response is the first response to my question Smile

However, I still have no idea what "EDIT TABLE menu" is. I suspect that it must be some GUI tool which has several menus, one of them being the "EDIT TABLE" menu. Maybe that GUI tool also has the "Preferences" menu item which allows you to set date format which will be used while displaying date values in the grid.

But, until we're told which GUI tool it is, I really don't know what to say.

[Updated on: Wed, 18 October 2017 12:05]

Report message to a moderator

Re: Changing the date format on a specific field [message #666185 is a reply to message #666183] Wed, 18 October 2017 13:34 Go to previous messageGo to next message
Alien
Messages: 291
Registered: June 1999
Senior Member
Sorry Littlefoot, my mistake Smile

I thought you were the OP, and didn't read too carefully.
You're probably right. Didn't think of that.

Report message to a moderator

Re: Changing the date format on a specific field [message #666250 is a reply to message #666185] Mon, 23 October 2017 07:41 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I'm sure when he said "EDIT TABLE", he thought that the date was being stored as a text string in the oracle table. The explanation by John should have set him straight.

Report message to a moderator

Previous Topic: virtual private database
Next Topic: Using Oracle Parallel hint
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 18:29:22 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.