Convert Date field in oracle

Greeting,
I have a date field in Oracle in dd-mmm-yy format. Now I want to convert it to MM/dd/yyyy format. How to do it in a select stmt in oracle?

Thanks
mrongAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
again

dates do NOT have formats,  only strings have formats


if   ? and ?  are strings in mm/dd/yyyy format then "between" doesn't make sense because they will be sorted in alphabetical order,  not date order.


compare dates to dates  and strings to strings

yourdate between to_date(?,'mm/dd/yyyy') and to_date(?,'mm/dd/yyyy').

0
 
sventhanCommented:
to_char(yourdate,'MM/dd/yyyy')
0
 
slightwv (䄆 Netminder) Commented:
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;
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

would give you

TO_CHAR(SY
----------
02/11/2011
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
sdstuberCommented:
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')
0
 
mrongAuthor Commented:
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.
0
 
mrongAuthor Commented:
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.

0
 
sdstuberCommented:
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').

0
 
sventhanCommented:
<  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
0
 
leewv1Commented:
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''
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.