Convert Date field in oracle

to_char(yourdate,'MM/dd/yyyy')

Dates are stored as a number in Oracle.  How they are 'displayed' is based on the NLS_DATE_FORMAT parameter of a TO_CHAR call:

select to_char(date_column,'MM/DD/YYYY') from table;

select to_char(sysdate,'MM/dd/yyyy') from dual

would give you

TO_CHAR(SY
----------
02/11/2011

>>dd-mmm-yy

I should have added that without a TO_CHAR and NLS_DATE_FORMAT set, the 'default' is DD-MON-YY which is what you are seeing.

date's don't have formats,  strings have formats

is your field really a date?

 if so, just select it with the format you want

if your field is really a string, then convert it to a date, then convert that date to a string


to_char(to_date(your_field,'dd-mmm-rr'), 'MM/dd/yyyy')

This won't work since I need to compare this date to date range below.

to_char(yourdate,'MM/dd/yyyy')  between ? and ?

? is in mm/dd/yyyy format.

thanks.

If I do the following comparison, it won't give me the correct output.

to_char(yourdate,'MM/dd/yyyy')  between ? and ?
? is in mm/dd/yyyy format.

if "yourdate" is actually a string then

you must convert it to a date too

to_date(yourdate,'whatever format is appropriate for this string')
           between to_date(?,'mm/dd/yyyy') and to_date(?,'mm/dd/yyyy').

<  Now I want to convert it to mm/dd/yyyy format

Please post your Table Structure or DATA types for all the 3 columns involved in this SQL.

If all columns are dates then

where yourdate1 between date2 and date3

You can do this:

SQL> alter session set NLS_DATE_FORMAT='MM/dd/yyyy';

use your select statement like:

select column_a, column_b from table  where date_column between  'first date' and 'second date' (formatted as MM/DD/YYYY for first and second date)

select column_a, column_b from table  where date_column between  '01/012011' and '01/31/2011''