Link to home
Create AccountLog in
Avatar of mrong
mrong

asked on

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
Avatar of sventhan
sventhan
Flag of United States of America image

to_char(yourdate,'MM/dd/yyyy')
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

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

ASKER

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.
Avatar of mrong

ASKER

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.

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
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''