-3

When I run this code: 

select trunc(TO_DATE(sysdate,'YYYY/MM/DD'),'Day')-43 from dual

Oracle returns: 

28/06/0027

I am using 'Day' for the trunc argument because I want go back 43 days from the beginning of the week. I need the data format to be 2015/07/11, and the data to be typed as a date, not char or anything else. I need this because I am using the date to filter on a table with a date column in the YYYY/MM/DD format.

How can I get the above SQL to give out YYYY/MM/DD typed as a date?

Zolt
  • 2,761
  • 8
  • 43
  • 60
  • 8
    Your requirements don't make sense. A `date` does not have a format (well, it has an internal binary representation that is not human readable). A `varchar2` that represents a date has a format. If you want a `date` data type, you cannot have a format. If you want a string formatted a certain way, you cannot have a `date`. If you want a `date`, `trunc(sysdate)-43`. – Justin Cave Aug 27 '15 at 20:30

1 Answers1

0

The date format on how it is viewed is controlled by the parameter NLS_DATE_FORMAT.

Please change it however you want. Try this.

alter session set nls_date_format = 'YYYY/MM/DD';
select sysdate from dual;

I am not concentrating my answer on the value of which date you want. I have answered the primary question on Formatting.

Please feel free to comment if you need any more assistance.

Varun Rao
  • 781
  • 1
  • 10
  • 31