Question about date column output

[ScottMattes]

Didn't there use to be a Preference for setting Date columns to output date and time? this is the first time that I have done much SQL Window queries and the date field is only showing the date portion. In the Command Window I see the date and time. I just went through the Preferences twice and do not see an option to make date field display both date and time. I did not see anything in the user's guide.

OK, wait a minute. I just opened a SQL Window (I had been in the results of an Execute SQL while debugging) and now I see the Date and Time format that I set in Preferences / NLS Options.

OK, so there is a bug in the Execute SQL while debugging window - it doesn't follow the NLS Options settings.

 

[Marco Kalter]

All output from the "Execute SQL" function in the debugger is formatted on the server, in accordance with the NLS settings of the Oracle Server. If you want to see the time fraction, you will either need to use a to_char() function in your SQL query, or set the NLS_DATE_FORMAT in your test script:

begin
  -- Modify the nls_date_format for the session of the test script
  dbms_session.set_nls('nls_date_format','''DD-MM-YYYY HH24:MI:SS''');
  -- Run the procedure
  xyz(p1 => :p1, p2 => :p2);
end;

You can of course also set the NLS_DATE_FORMAT in your Oracle Registry, as an environment variable, or as a PL/SQL Developer parameter.

 

[Hilarion]

As far as I know the SQL Window will show the time portion of dates as long as it's not 00:00:00 (exact midnight). (Actually it'd be nice if there was an option in PL/SQL Developer NLS settings to always show time portion of date expressions.)

As Marco wrote, you can use "to_char( my_date_expression )" in SQL queries to have the Oracle session NLS settings kick in (not the PL/SQL Developer NLS settings) or give specific format like "to_char( my_date_expression, 'YYYY-MM-DD HH24:MI:SS' )".

 

[ScottMattes]

OK, so that window handles output from a query differently than a SQL Window does, I can live with that (but it should be documented, or more clearly documented in the help).

I had just thought that the output should be consistant.