Oracle sql date query formats single-digit

user_5888946 0
Viewed 537

I need to retrieve dates from Oracle as Java SimpleDateFormat M/D/YYYY (for instance, Independence Dat would be 7/4/1776). However, looking at https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm#OLADM780 there appears only to be a way to retrieve MM/dd/yyyy (07/04/1776).

Are there any tricks to retrieve the date the original way? I suppose I could play around with all sorts of if IF statements, and substrings, but that seems quite complicated.

That is to say

 select to_char(sysdate, 'MM/DD/YYYY') from dual    works

but

 select to_char(sysdate, 'M/D/YYYY') from dual    does not.
date datetime oracle sql string
2 Answers

You can use format modifier fm for this:

select to_char(sysdate, 'fmMM/DD/YYYY') formated_date from dual

Demo on DB Fiddle:

 select to_char(date '1776-07-04', 'fmMM/DD/YYYY') formated_date from dual

| FORMATED_DATE |
| :------------ |
| 7/4/1776      |
user_106767160

Regular expressions are your friend here:

WITH cteDate AS (SELECT TO_DATE('01/02/2020', 'DD/MM/YYYY') AS SOME_DATE FROM DUAL)
SELECT TO_CHAR(SOME_DATE, 'MM/DD/YYYY') AS FORMATTED_SOME_DATE,
       REGEXP_REPLACE(TO_CHAR(SOME_DATE, 'MM/DD/YYYY'), '0([0-9])/', '\1/') AS FIXED_DATE
  FROM cteDate

The above returns

FORMATTED_SOME_DATE    FIXED_DATE
02/01/2020             2/1/2020

To explain what's going on here: The search expression '0([0-9])/' says "Look for a substring which starts with the '0' character, is followed by one character in the range '0' to '9', and is finished with a '/' character. Treat the character in the range '0' to '9' as a group'.

The replacement expressions '\1/' says "Replace what you found in the search string with the first grouped expression (there's only one), followed by a slash character'.

user_2131360
Related