Convert number to date sql oracle

I'm trying to convert a number (yyyymmdd) to date (mm/dd/yyyy)

For example

20150302 ====> 03/02/2015

5 Answers

You can try this:

select to_date(20150302,'yyyymmdd') from dual;

or

select to_char(to_date(20150302,'yyyymmdd'),'mm/dd/yyyy') from dual;

1

You can use TO_DATE function to convert NUMBER to DATE. Try in following:

SELECT TO_DATE(20150302, 'YYYYMMDD') FROM DUAL

4

The above answer is still incorrect. It returns a character string when a date is needed. The correct way is:

select to_date(to_char(20210416), 'YYYYMMDD') num_to_char_to_date from dual;

2

TO_DATE accepts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype and converts into a value of DATE datatype.

So, convert the number into string, and apply to_date. You could use single-quotation marks around the number to convert it into string.

SELECT TO_DATE('20150302', 'YYYYMMDD') FROM dual;

Remember, a DATE has no format, what you see is for display purpose. If you want to display the date in a desired format, then use TO_CHAR along with your desired format model.

SELECT TO_CHAR(TO_DATE('20150302', 'YYYYMMDD'), 'mm/dd/yyyy') FROM dual;

Read more about TO_DATE.

3

To convert in the required format:

select to_char(to_date(20150302, 'YYYYDDMM'), 'mm/dd/yyyy')

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy