Oracle's SQL Developer Tool: Setting either session or default date formatting

by Heathesh 11. June 2010 00:36

When working with date fields in Oracle's SQL Developer, it's important to note that tools like SQL developer have different ways to set how the date is displayed in result sets. For example I believe the standard date display format in SQL developer is DD/MON/RR which would look something like 11/JUN/10.

There are two ways to change this date format. The one is to alter the current SQL Developer session and set the date format for your current session alone. This is done by running the following command in any SQL Developer Worksheet:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:mi:ss';


That will change the date format for the current session, i.e. the connection to the current database you are running queries against. This means any worksheets you open that connect to the database you ran the query against will have the specified data format for the duration of your connection to that database, and any worksheets that connect to any other database will retain the default date format as they exist in separate sessions.

You can also set the default date format for SQL developer, this can be done inside SQL Developer:

1. Select "Tools" -> "Preferences"
2. Expand the "Database" node and click on "NLS"
3. You will see a text box with the label "Date Format", simply enter your desired default date format there

Oracle's date formatting options include:

MM Numeric month (e.g., 06)
MON Abbreviated month name (e.g., JUN)
MONTH Full month name (e.g., JUNE)
DD Day of month (e.g., 11)
DY Abbreviated name of day (e.g., FRI)
YYYY 4-digit year (e.g., 2010)
YY Last 2 digits of the year (e.g., 10)
RR Like YY, but the two digits are 'rounded' to a year in the range 1950 to 2049. Thus, 10 is considered 2010 instead of 1910
AM (or PM) Meridian indicator
HH Hour of day (1-12)
HH24 Hour of day (0-23)
MI Minute (0-59)
SS Second (0-59)

Happy dating!

Tags: , , , ,

Oracle | SQL Developer



Powered by BlogEngine.NET 1.5.0.7 (with enhancements by Heathesh)
Theme by Mads Kristensen (with tweeks by Heathesh)

Certifications

Microsoft Certified Professional

Microsoft Certified Technology Specialist

Answer Questions

 

Tag cloud

Calendar

<<  June 2017  >>
MoTuWeThFrSaSu
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789

View posts in large calendar

http://heathesh.com