refomratting date field

[zoey]

Hi,

I would like to see if anyone knows how to reformat a date field so it will print the date out in the following format:

DD/MM/YYYY

It would also be nice if I could input the date into this date field as the following:

DDMMYYYY

Does anyone know how to fix this? Currently, our dates are set up to output like this:


23-Mar-2001

Thanks.
Zoey

[pangul]

if you just want the output then use


to_char(date, dateformat)


so for 23/03/01

to_char('23-mar-2001', 'dd/mm/yy')

or for 230301

to_char('23-mar-2001', 'ddmmyy')


If you want to change the date format and don't want to convert the data type then you can do that in you machine by setting the

NLS_DATE_FORMAT parameter

or you can also change for a session

alter session set nls_date_format='dd/mm/yyyy';

or for the data base

[sreddy]

I will not advise you to set this parameter at database level , but session level specially when you have remote database connections like db links etc.,

[crjangel]

I can not get this to work.

I am trying to truncate the date from the database and just display in output the date and not the time.

I am outputting it as a variable in cold Fusion as #Date#

in the query it doesn't recognize the function. Am i putting this in the wrong place in relation to the variable? What is the proper syntex when using a variable?