1

I'm using oracle 11g sql developer

I have a varchar2 column with dates as 0523 (mmDD).

I want to convert them to a date column and have them look like 23-05 (dd-mm)..

Any ideas?

SeattleGray
  • 95
  • 1
  • 3
  • 10

1 Answers1

2

Well, you can do string operations directly to get the format you want:

substring(c, 3, 2)||'-'||substring(c, 1, 2)

To convert to a date, you can use:

to_date('2012'||c, 'YYYYMMDD')

To convert a date back to the form you want:

to_char(<date>, 'DD-MM')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    The solution uses c to refer to the date column you have. || is concatenate so ||c is adding the date to the year 2012. Not quite a full solution as you haven't given much for schema...a combination of the 3 statements here will be what you need. +1 answer – Twelfth Jun 27 '12 at 16:59