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!
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)