Question 113386 in Mysql Question posted in
The official MySQL documentation can be found here.

date format in oracle/mysql

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
Tags:

2

Answers


  1. 0

    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>
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search