date format in oracle/mysql-CodePudding

Help me to populate below columns as date without using to_date or to_char functions.

day	month	year
1	2	1995
2	3	1998
5	6	2020

output

date
01-02-1995
02-03-1998
05-06-2020

CodePudding user response：

If Oracle, then concatenation of zero left-padded values might do the job (see line #7):

SQL> with test (day, month, year) as
  2    (select 1, 2, 1995 from dual union all
  3     select 2, 3, 1998 from dual
  4    )
  5  select day, month, year,
  6         --
  7         lpad(day, 2, '0') ||'-'|| lpad(month, 2, '0') || '-'|| year as result
  8  from test;

       DAY      MONTH       YEAR RESULT
---------- ---------- ---------- ----------------------------------------------
         1          2       1995 01-02-1995
         2          3       1998 02-03-1998

SQL>

CodePudding user response：

If you want query without to_date and to_char. Let try below query:

SELECT 
    LPAD(EXTRACT(DAY FROM DATE (year ||  '-' || month || '-' || day)),2,'0') 
    || '-' 
    || LPAD(EXTRACT(MONTH FROM DATE (year ||  '-' || month || '-' || day)),2,'0') 
    || '-' 
    || EXTRACT(YEAR FROM DATE (year ||  '-' || month || '-' || day))
FROM YOUR_TABLE;

CodePudding user response：

You should use TO_DATE as that is what it is designed for.

However, as an academic exercise, if you start with DATE '0001-01-01' and then use, for Oracle, ADD_MONTHS (or for MySQL, TIMESTAMPADD) for the years and months and addition for the days:

In Oracle:

SELECT t.*,
       TO_DATE(year || '-' || month || '-' || day, 'YYYY-MM-DD') AS dt,
       ADD_MONTHS(DATE '0001-01-01', 12 * (year - 1)   (month - 1))   (day - 1) AS dt2
FROM   table_name t

Which, for the sample data:

CREATE TABLE table_name(day, month, year) AS
SELECT 1, 2, 1995 FROM DUAL UNION ALL
SELECT 2, 3, 1998 FROM DUAL UNION ALL
SELECT 5, 6, 2020 FROM DUAL;

Outputs:

DAY	MONTH	YEAR	DT	DT2
1	2	1995	1995-02-01 00:00:00	1995-02-01 00:00:00
2	3	1998	1998-03-02 00:00:00	1998-03-02 00:00:00
5	6	2020	2020-06-05 00:00:00	2020-06-05 00:00:00

fiddle

Or MySQL:

SELECT t.*,
       TIMESTAMPADD(MONTH, 12 * (year - 1)   (month - 1), DATE '0001-01-01')
           (day - 1) AS dt
FROM   table_name t

Which outputs:

day	month	year	dt
1	2	1995	19950201
2	3	1998	19980302
5	6	2020	20200605

fiddle