Problem with a date format 1

[Zargo]

Hi all,

I have an Oracle database and in one of the tables the dateformat is stored like:

31-12-2005
1-1-2006
1-2-2006
28-2-2006

I want to convert these dateformats to DDMMYYYY, how can i do this? I have tried

Select to_date(datecolumn,DDMMYYYY) from mytable..without success. I could only select some information and i have no rights to use SET NLS DATEFORMAT or something.

Anybody idea how i can achieve the right conversion?

TIA

 

[oradba101]

Hi, Zargo

Perhaps TO_CHAR(date_column,'DDMMYYYY')

Regards,


William Chadbourne
Oracle DBA

 

[Dagon]

You don't have to change NLS_DATE_FORMAT at system level. You can set it at session level as well using:

alter session set nls_date_format = 'DDMMYYYY';

If your column is already a date, then you won't be able to use to_date on it. To_date is designed to convert character strings to dates, not change the appearance of a date.

 

[Zargo]

Thanks both!! it works, i have tried the function to_char...