1

I have a character field that stamps in the order of MMDDYYHHMMSS (note: not a date but character field). I am wanting to kick this out to a date field in my SQL into this format dd.mm.yyyy. hh24:mi.

My problem is that the sql kicks it out to YYYY-MM-DD field without the time. This section of the sql looks like this:

TO_DATE(SUBSTR(MOPACTIVITY.MOPID,3,2)||'.'||SUBSTR(MOPACTIVITY.MOPID,1,2)
        ||'.'||'20'||SUBSTR(MOPACTIVITY.MOPID,5,2)||'.'||SUBSTR(MOPACTIVITY.MOPID,7,2)
        ||':'||SUBSTR(MOPACTIVITY.MOPID,9,2)||':'||SUBSTR(MOPACTIVITY.MOPID,11,2)
       , 'dd.mm.yyyy. hh24:mi:ss') "XXX",

Any thoughs on how to get the time to convert too?

Ben
  • 51,770
  • 36
  • 127
  • 149
  • possible duplicate of [comparing date with a predefined format pl sql](http://stackoverflow.com/questions/13775791/comparing-date-with-a-predefined-format-pl-sql) – Ben Jun 03 '13 at 20:33
  • I have a comprehensive example of what's happening in [this answer](http://stackoverflow.com/questions/15970277/comparing-with-date-in-oracle-sql/15970516#15970516), though I'm sure there's plenty more out there... The simple answer is `to_char(to_date(,'MMDDYYHHMMSS'), 'DD.MM.YYYY. HH24:mi')` – Ben Jun 03 '13 at 20:34

1 Answers1

1

No need for such a complicated expression:

to_date(MOPID, 'MMDDYYHH24MISS')

will convert the column to a real DATE column assuming the time part is in 24 hour format (00-23, not 00-12). And this will also fail if you don't really have valid dates in the varchar column.

this out to a date field in my SQL into this format

A DATE column does not have "a format"!

The format is only applied when you display it.

In case you mean you want to convert the varchar stored in your column into another varchar that has a different date formatting, the easiest is probably to simply convert the above expression back to a varchar:

to_char(to_date(MOPID, 'MMDDYYHH24MISS'), 'dd.mm.yyyy. hh24:mi')

Before applying something like that, allow me one comment:

Store dates in DATE columns, never ever store them in a VARCHAR column.

If you had done that from the beginning, all you would have to do know is to simply apply a single to_char() to your DATE column to get the display format you want.