Link to home
Create AccountLog in
Avatar of mrong

asked on

Convert Date field in oracle

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?

Avatar of sventhan
Flag of United States of America image

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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


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')
Avatar of mrong


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.

Avatar of mrong


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.

Avatar of Sean Stuber
Sean Stuber

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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''