'Changing values and Concatenating into readable Dates

I have 2 columns called MONTH, and YEAR.

The YEAR is just pulling in as INT and contains values such as

  • 2017
  • 2018
  • 2019

etc...

The MONTH column however is pulling in as:

  • 01-JAN
  • 02-FEB
  • 03-MAR
  • 04-APR
  • 05-MAY

etc...

I need a readable date out of this, ideally your typical short date:

  • 01/01/2019
  • 02/01/2019
  • 06/01/2019

I am unfamiliar with CASE functions (actually I'm just unfamiliar with getting them to work without errors)

SELECT
MONTH as "Month",
YEAR as "Year",
CASE
WHEN MONTH = "01-JAN" THEN "01/01/"
WHEN MONTH = "02-FEB" THEN "02/01/"
WHEN MONTH = "03-MAR" THEN "03/01/"
WHEN MONTH = "04-APR" THEN "04/01/"
WHEN MONTH = "05-MAY" THEN "05/01/"
WHEN MONTH = "06-JUN" THEN "06/01/"
WHEN MONTH = "07-JUL" THEN "07/01/"
WHEN MONTH = "08-AUG" THEN "08/01/"
WHEN MONTH = "09-SEP" THEN "09/01/"
WHEN MONTH = "10-OCT" THEN "10/01/"
WHEN MONTH = "11-NOV" THEN "11/01/"
WHEN MONTH = "12-DEC" THEN "12/01/"
END as MONTH_TEXT
FROM
ABCDE.HIJ_KLM_NOP_QRS_VW

I am assuming two different things will be occurring here... changing the MONTH column from its current form (i.e. 01-JAN) to a readable MONTH/DAY format that would look something like 01/01/

Then concatenating that with YEAR

CONCATENATE(MONTH_TEXT,YEAR)

Any help would be greatly appreciated.

sqloracle


Solution 1:[1]

You can try this:

with dataa as
(SELECT '2019' AS YEAR, '01-JAN' AS MONTH FROM DUAL UNION ALL
SELECT '2019' AS YEAR, '02-FEB' AS MONTH FROM DUAL UNION ALL
SELECT '2019' AS YEAR, '03-MAR' AS MONTH FROM DUAL UNION ALL
SELECT '2018' AS YEAR, '30-DEC' AS MONTH FROM DUAL
)
SELECT
    TO_CHAR(TRUNC(TO_DATE(MONTH
                          || '-'
                          || YEAR, 'DD-MON-YYYY'), 'MONTH'), 'MM/DD/YYYY') 
    AS YOUR_OUTPUT
FROM
    DATAA

DB Fiddle Demo

Output:

enter image description here

I have truncated(as per my understanding from the question) the date to start of the month. If you do not want this then you can remove trunc(....,'month') from the above code.

Cheers!!

Solution 2:[2]

Try this, it will give first two characters of your month column, then add /01/ and then the year. 

SELECT (SUBSTRING(MONTH, 1, 2) + "/01/" + YEAR) as ShortDate FROM ABCDE.HIJ_KLM_NOP_QRS_VW

Solution 3:[3]

Try this:- 

SELECT concat(
            CASE WHEN MONTH = "01-JAN" THEN "01/01/" 
            WHEN MONTH = "02-FEB" THEN "02/01/" 
            WHEN MONTH = "03-MAR" THEN "03/01/" 
            WHEN MONTH = "04-APR" THEN "04/01/" 
            WHEN MONTH = "05-MAY" THEN "05/01/" 
            WHEN MONTH = "06-JUN" THEN "06/01/" 
            WHEN MONTH = "07-JUL" THEN "07/01/" 
            WHEN MONTH = "08-AUG" THEN "08/01/" 
            WHEN MONTH = "09-SEP" THEN "09/01/" 
            WHEN MONTH = "10-OCT" THEN "10/01/" 
            WHEN MONTH = "11-NOV" THEN "11/01/" 
            WHEN MONTH = "12-DEC" THEN "12/01/" END, '/', Year
       ) as Added_date 
FROM table

Output is :

enter image description here

Solution 4:[4]

You can use LEFT to grab the month, and use MAKEDATE to output as a Date:

Select MAKEDATE(YEAR, substr(Month,1,2),'01') from mytable

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Popeye
Solution 2 Colin MacMillan
Solution 3 Amarat
Solution 4 General Grievance

Related Questions