Oracle SQL: Date with words, Sort by Day of the Week


Case question:

Display each employee’s last name, hire date, and salary review date, which is the first Tuesday after a year of service, but only for those hired after 1997.
Label the column REVIEW DAY.
Format the dates to appear in the format similar to
TUESDAY, August the Thirty-First of year1998

Answer:


 1  SELECT last_name, hire_date, TO_CHAR (hire_date, 'DAY "," Month "the"
Ddsp "of year" YYYY') "Review Day"
  2  FROM EMPLOYEES
  3* WHERE TO_CHAR(hire_date, 'DY')  = 'TUE' and hire_date > '1-Jan-1997'

Ouput:

LAST_NAME HIRE_DATE
————————- ———
Review Day
—————————————————
Mourgos 16-NOV-99
TUESDAY , November the Sixteen of year 1999

Taylor 24-MAR-98
TUESDAY , March the Twenty-Four of year 1998

Advertisements

About Anatoly Spektor

My name is Anatoly Spektor (originally Anatolijs Spektors) I am Software and Web Developer. I have worked in Seneca Center for Development of Open Technology on Big Blue Button Add-on - Polling Module, Red Hat and some other places :) I am an author of the book 'Eclipse Debugging How To', Muay Thai fighter and amateur photographer ;)
This entry was posted in Database, ORACLE, SQL and tagged , , , . Bookmark the permalink.

2 Responses to Oracle SQL: Date with words, Sort by Day of the Week

  1. long distance movers orlando says:

    Hey what i can do to convert my date from one format to another for comparision which can be used in stored procedure in which the date is in the format of getdate()

  2. Awesome issues here. I’m very glad to peer your post. Thank you a lot and I am looking forward to touch
    you. Will you please drop me a mail?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s