SQL - Date Format

[Chris]

I use Textpad to run SQL selects against Oracle. I want to know if there is a way to set the date format globally, so that all my date fields come out as full date and time, rather than just date.
Is there some setting I can use within Textpad or my textpad file to do this?
For example, select * from emp; The emp table has two date columns. I want the results of the sql displayed such that the date columns showing something like 2001/06/22 01:22:23 rather than 22-JUL-01.

thanks

[Chris]

Your question is more of an Oracle question than a Textpad question - however:

You will need to select each column seperately to format the date the way you want it. Use the "to_char()" function.

so rather than select * from emp you need to specify the fields.

select firstname, lastname, to_char(date, 'yyyy/mm/dd hh:mi:ss') from emp;
or
select to_char(date, 'yyyy/mm/dd hh24:mi:ss') from emp; - if you want 24 hour "military" time.

I am looking for a syntax file that has all of Oracle`s built in functions listed - anybody got one that has the complete list ?

[Chris]

Just put the following line at the front of your SQL

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