CODEWITHSUNDEEP
oracle-databaseplsql
Vimal Bhaskar
Vimal Bhaskar

Reputation: 778

Date Conversion in Oracle (YYYYMMDD format back to YYYYMMDD)

Hi I am pretty much new to Oracle and need an idea on how to do it.

Consideer I have a Text file and the value in the file is

'20180924' '20180923'

I read these value from the Pro*c and then I add 29 days to it, so the value now changes from 20180924 to 20180953. I don't want that to happen i want the value to be added as 

 TO_DATE('20180924','yyyymmdd')+29 which returns the value as "23-OCT-18"

The answer is correct but i want the result to also be in the same YYYYMMDD format like 20181023.

How do i achieve this ?

Upvotes: 3

Views: 32400

Answers (3)

Durgakumar Sunkara
Durgakumar Sunkara

Reputation: 1

select TO_NUMBER((TO_CHAR(TO_DATE(HIREDATE,'DD-MON-YY'),'YYYYMMDD')) FROM EMP;

select TO_NUMBER((TO_CHAR(TO_DATE(HIREDATE,'DD-MON-YY'),'YYYYMMDD'))FROM EMP

20201101

select TO_NUMBER((TO_CHAR(TO_DATE(SYSDATE,'DD-MON-YY'),'YYYYMMDD')) FROM DUAL;

Upvotes: -2

MT0
MT0

Reputation: 167784

Dates do not have a format - they are represented internally by 7 or 8 bytes.

If you want a date to have a format then you will need to convert it to a data type that can be formatted - i.e. a string:

TO_CHAR( TO_DATE( '20180924', 'yyyymmdd' ) + 29, 'YYYYMMDD' )

When SQL/Plus (or SQL Developer) displays date data types they implicitly convert them to strings (since this is more meaningful to you, the user, than displaying the raw bytes) and uses the NLS_DATE_FORMAT session parameter as the format model in this implicit conversion. If you want to change this then you can use:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';

and then your query:

TO_DATE( '20180924', 'yyyymmdd' ) + 29

should (provided your SQL client uses this setting and does not have their own internal format preferences) give the output you expect. (Note: this will change the default format for the client to display all dates in this session and not just this one statement.)

Upvotes: 4

Ronnis
Ronnis

Reputation: 12833

Just format it back.

to_char(TO_DATE('20180924','yyyymmdd')+29, 'yyyymmdd')

Upvotes: 8

Related Questions