A nice query to have when dealing with DATES in Oracle

Dates in Oracle. 

It's not always easy, whereas in fact it should all be plain and simple.

Only too often I've had problems with dates myself in the past. The other day I was asked by someone to have a look at his code and while debugging the source of the problem was ... yup, a wrongly interpretted date format.

So I thought digging up a query I made once when I gave a beginners training for SQL. This is a query that might put you in the right direction ... I hope it's useful for you!

The ':' is used for you to enter a date in the given format. You may need to replace this ':' depending on which query tool you're using.

select to_date( ':ddmmyyyy', 'ddmmyyyy') date_reference

     , to_char( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'day') weekday

     , to_char( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'IW') week_number

     , trunc( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'yyyy') start_year

     , trunc( add_months( to_date( ':ddmmyyyy', 'ddmmyyyy'), 12), 'yyyy') - 1  end_year

     , trunc( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'IW') start_week

     , trunc( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'IW') + 6 end_week

     , trunc( trunc( to_date( ':ddmmyyyy', 'ddmmyyyy'), 'yyyy'), 'IW') start_week_one

  from dual

  ;

Happy to share!