DDL for table:

  CREATE TABLE "AUTH_USER_REGISTRATION" 
   (    "USER_ID" NUMBER(*,0), 
    "USER_NAME" VARCHAR2(200 BYTE), 
    "MAIL" VARCHAR2(400 BYTE), 
    "FULLNAME" VARCHAR2(200 BYTE), 
    "GENDER" VARCHAR2(50 BYTE), 
    "USER_DAY" VARCHAR2(10 BYTE), 
    "USER_MONTH" VARCHAR2(50 BYTE), 
    "USER_YEAR" VARCHAR2(50 BYTE), 
    "CREATED" NUMBER(38,0), 
    "STATUS" VARCHAR2(10 BYTE), 
    "UU_ID" VARCHAR2(100 BYTE), 
    "MOBILE" VARCHAR2(100 BYTE), 
    "CHANGED" NUMBER(38,0)
   );

Insert Statements:

Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (914981,'[email protected]','[email protected]','Birendra Mohan',null,'05','March','1977',1412433302,'1','69945218-cc7f-46f5','9471067',null);
Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (916653,'[email protected]','[email protected]','MAHESH DEOCHAKE','male','18','July','1989',1412439002,'1','4563adc0-345b-4779-a8ee','7709910',null);
Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (916665,'[email protected]','[email protected]','Harshal Jhaveri',null,'08','May','1992',1412439002,'1','a10a3f0c-5892-4579-aef6-46a','8568013',null);
Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (890991,'[email protected]','[email protected]','subramani gunasekaran','male','17','October','1950',1412358602,'1','6b4c7077-8864-4702-','44697999',null);
Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (891053,'[email protected]','[email protected]','AMIR RANJAN DHANI','male','20','August','1992',1412358602,'1','62dd0cb4-c86b-4634-ba6','9432880',null);
Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (891055,'[email protected]','[email protected]','Praveen Singh Chouhan','male','10','April','1990',1412358602,'1','037f1079-db85-49d7','04663542',null);
Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (892719,'[email protected]','[email protected]','Muhammed Basheer','male','05','July','1994',1412362502,'1','10c07ce7-badf-4204-b2a9','7232903',null);
Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (892729,'[email protected]','[email protected]','swapnil papinwar','male','08','September','1986',1412362502,'1','2f8940ef-3136-425','9818122',null);
Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (896209,'[email protected]','[email protected]','Rajiv Nayan','male','10','June','1969',1412388301,'1','8008f636-916b-4982-baad-','9810769',null);
Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (465,'Jaysparikh','[email protected]','Jay Parikh','male','31','January','1984',1406359580,'1','c04b7129-b04e-4ca5-9199-','9586817',null);
Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (476,'sknyer','[email protected]','Sasidharan Nair','male','26','October','1968',1406359591,'1','69288f05-bfbb-4f5e-9561-71e','530154',null);
Insert into AUTH_USER_REGISTRATION (USER_ID,USER_NAME,MAIL,FULLNAME,GENDER,USER_DAY,USER_MONTH,USER_YEAR,CREATED,STATUS,UU_ID,MOBILE,CHANGED) values (504,'casstifer','[email protected]','Aditya Verma','male','25','December','1995',1406359633,'1','a4e8ad7b-a5f8-476b-a779-','742842',null);

Query:

SELECT
    user_id as user_id,
    user_name as user_name,
    mail as user_mail,
    fullname as full_name,
    case when (gender='male' or gender='mail' or gender='Male') then 'Male'
    when (gender='female' or gender='Female') then 'Female' else 'Others'
    end as gender,
    case
    when user_month='January' then '1'
    when user_month='February' then '2'
    when user_month='March' then '3'
    when user_month='April' then '4'
    when user_month='May' then '5'
    when user_month='June' then '6'
    when user_month='July' then '7'
    when user_month='August' then '8'
    when user_month='September' then '9'
    when user_month='Ocotober' then '10'
    when user_month='November' then '11'
    when user_month='December' then '12' 
    end user_month,
    case 
    when (user_day is not null or user_day<>'') and (user_month is not null or user_month<>'')   and (user_year is not null or user_year<>'') 
    then  user_day ||'-' || user_month ||'-' || user_year else '' end as dob,
    created,
    status as status,
    uu_id as uuid,
    mobile as mobile,
    changed 
FROM
    auth_user_registration;

I need to concat the user_day, user_month and user_year into single column like this (01-01-2022) format current format is (01-January-2022). Please help me to do this.

CodePudding user response：

First of all, I'd say that you're doing it wrong. You should store dates as dates (into a DATE datatype column), not separate days/months/years into their own columns, with no control over it. What if you entered 'Janiary'? That's obviously January, but - to recognize it as such - you'd have to put (a lot?) of effort into it. Or, why wouldn't day be 84? 84-Janiary-2022; doesn't look OK.

Anyway:

• first concatenate those values
• apply to_date function to the result, with appropriate format model
• then apply to_char to that DATE datatype value, using target format model

SQL> SELECT user_day,
  2         user_month,
  3         user_year,
  4         TO_CHAR (
  5            TO_DATE (
  6               user_day || '-' || user_month || '-' || user_year
  7                  DEFAULT NULL ON CONVERSION ERROR,
  8               'dd-month-yyyy',
  9               'nls_date_language=english'),
 10            'dd-mm-yyyy') AS dob
 11    FROM auth_user_registration;

USER_DAY   USER_MONTH      USER_YEAR       DOB
---------- --------------- --------------- ----------
05         March           1977            05-03-1977

SQL>

I'd rather suggest the following approach:

SQL> CREATE TABLE auth_user_registration
  2  (
  3     user_id     NUMBER (*, 0),
  4     user_name   VARCHAR2 (200 BYTE),
  5     dob         DATE                       --> DATE datatype
  6  );

Table created.

SQL> INSERT INTO auth_user_registration (user_id, user_name, dob)
  2       VALUES (914981, '[email protected]', DATE '1977-03-05');

1 row created.

SQL> SELECT dob, TO_CHAR (dob, 'dd-mm-yyyy') result FROM auth_user_registration;

DOB      RESULT
-------- ----------
05.03.77 05-03-1977

SQL>

P.S. Consider NOT to use double quotes while working with Oracle; they bring problems.

CodePudding user response：

You already have a case expression to convert month names to numbers, but you seem to be expecting the reference to user_month in your concatenation to use the result of that case, because you aliased it with the same name. That won't work; you can't user a column alias in the same level of query it is defined (except in an order-by clause), so that is still referring to the original table column with the same name - hence you seeing the month names.

You need to move the case expression inside the concatenation. But you've also spelled 'October' wrong, and from your example you want zero-padded month numbers; and a simple case expression would be a bit less typing than a searched one here. So you could do:

SELECT
    user_id as user_id,
    user_name as user_name,
    mail as user_mail,
    fullname as full_name,
    case when (gender='male' or gender='mail' or gender='Male') then 'Male'
    when (gender='female' or gender='Female') then 'Female' else 'Others'
    end as gender,
    case 
    when user_day is not null and user_month is not null and user_year is not null
    then user_day ||'-'
      || case user_month
        when 'January' then '01'
        when 'February' then '02'
        when 'March' then '03'
        when 'April' then '04'
        when 'May' then '05'
        when 'June' then '06'
        when 'July' then '07'
        when 'August' then '08'
        when 'September' then '09'
        when 'October' then '10'
        when 'November' then '11'
        when 'December' then '12' 
      end
      ||'-' || user_year end as dob,
    created,
    status as status,
    uu_id as uuid,
    mobile as mobile,
    changed 
FROM
    auth_user_registration;

fiddle

Note though that if you have a month name that isn't in the list, or is spelled wrong, or is in a different (typographic) case (e.g. all lowercase, 'january'), then the case expression will evaluate to null so you could end up with something like 84--2022 to borrow Littlefoot's example.

But it would be simpler to convert to and from a date as Littlefoot has shown, or to follow normal practice and just store a single date value instead of three separate string values, for all the reasons already given.