1

Good afternoon almighty Stackoverflow!

I'm not overly familiar with SQL in Oracle, but have a need to take a date/time value and convert it to a string that matches a specific format for another application. I found a lot of scenarios that were similar, but those mixed with some Oracle documentation has not gotten me to what I need yet.

The input format is as follows: 8/6/2014 3:05:21 PM

The format that I need to be input into the other application is as follows: YYYYMMDDhhmmssuu

uu is microseconds (fractional seconds in Oracle I guess).

What I thought would work would be: 

to_date(VP_ACTUAL_RPT_DETAILS.ETLLOADER_OUT,'YYYYMMDDHH24MISSFF')

I think that only works if the input format matches the output format.

If you can provide assistance, I would greatly appreciate it!

billhubb84
  • 195
  • 4
  • 13
  • Check this link: http://stackoverflow.com/questions/1758219/string-to-date-in-oracle-with-milliseconds. I think it can be useful – Aramillo Aug 21 '14 at 20:03

4 Answers4

3

If You convert from DATE type to output format use TO_CHAR function:

TO_CHAR(VP_ACTUAL_RPT_DETAILS.ETLLOADER_OUT,'YYYYMMDDHH24MISSFF')

If You convert from VARCHAR2 type, then use both functions:

TO_CHAR(TO_DATE(VP_ACTUAL_RPT_DETAILS.ETLLOADER_OUT, 'MM/DD/YYYY HH:MI:SS'),'YYYYMMDDHH24MISSFF')

TO_DATE - converts from VARCHAR2 type (input format) to DATE type; TO_CHAR - from DATE type to VARCHAR2 type (output format)

Rimas
  • 5,904
  • 2
  • 26
  • 38
  • Thank you much, helps me understand better what I'm working with. Looks like on this databases backend there are no fractional seconds and the time was not actually 24 hour, so made two minor changes to the first solution and it worked perfectly. Thanks a lot for the quick and concise response! – billhubb84 Aug 21 '14 at 20:24
0

select to_char(systimestamp,'yyyymmddhh24missFF') from dual;

JM_developer
  • 58
  • 5
0

The Oracle function you need is TO_CHAR.

to_char(VP_ACTUAL_RPT_DETAILS.ETLLOADER_OUT,'YYYYMMDDHH24MISSFF')

Tim Boyer
  • 1
  • 1
0

since your format is looking for a string variable I am unsure if it will take a to_date.

My advice would be experimenting with combining both the to_char() and to_date() functions

to_char(to_date(VP_ACTUAL_RPT_DETAILS.ETLLOADER_OUT,'DD/MM/YYYY HH:MM:SS),'YYYYMMDDHH24MISSFF')

or 

to_date(to_char(VP_ACTUAL_RPT_DETAILS.ETLLOADER_OUT,'YYYYMMDDHH24MISSFF'),''DD/MM/YYYY HH:MM:SS')

are functions I would try. Oracle can be very picky about inputs with dates/strings, I am relatively new to Oracle as well and times/dates are often frustrating

Taku_
  • 1,505
  • 3
  • 14
  • 22