1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Changing the date format on a specific field

Discussion in 'General' started by acheo, May 7, 2015.

  1. acheo

    acheo Active Member

    Likes Received:
    Trophy Points:

    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.

  2. ocprep

    ocprep Forum Advisor

    Likes Received:
    Trophy Points:
    Orlando, Florida
    Date fields do not have a format in that sense. Dates in Oracle are always stored in a proprietary numeric format. When a string value representing a date (like '12-NOV-2014') is inserted into a date field, Oracle converts that string into the numeric representation that Oracle stores dates in. If the data is later SELECTed from the table, Oracle converts the numeric date format into a string for display to the users.

    By default, both the string-to-numeric and numeric-to-string conversions make use of the NLS_DATE_FORMAT value active in the current session to implicitly. If you want to convert a date in a format other than what is represented by the NLS_DATE_FORMAT (either direction), you must make use of the TO_DATE or TO_CHAR functions. Using the NLS_DATE_FORMAT is called implicit conversion. Using TO_CHAR and TO_DATE is explicit conversion.