working with date and time
i need a query that will display time as wednesday the 19 of august 2012 13:44:13
The DATE datatype is stored internally by Oracle in 7 bytes in a format that gives date (time) precision to one second (i.e. the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND are all stored) and valid dates range from 1st January 4712 BC, to 31st December 9999 AD. This format is independent of how the date is displayed when fetched from the database.
Therefore the short answer to this question is as follows:-
select to_char(sysdate,'Day ddth "of" Month yyyy' ) from dual
But let's explain how we got there.
The default date format is specified by the NLS_DATE parameter or if this is not set by the NLS_TERRITORY to avoid confusion when dealing with numeric dates such as 1/8/2012. In the US that would be interpreted as 8th January 2012 whereas in the UK it would be interpreted as 1st August 2012.
Oracle also provides standard character strings which we can use to convert the date to a particular format we specify.
For example we can display the date in numeric format with a query such as:
select to_char(sysdate,'dd/mm/yyyy') from dual
which would display 1st August 2012 as 01/8/2012.
We can also ask Oracle to spell out the day of the week and the month by using the reserved words "DAY" and "MONTH". Note that the case of the literal matches the case of the reserved word, so the output of the following statement would be "SEPTEMBER September september"
SELECT TO_CHAR(SYSDATE,'MONTH')||' '||TO_CHAR(SYSDATE,'Month')||' '||TO_CHAR(SYSDATE,'month') FROM DUAL;
Oracle also allows us to use separators such as "-"and "/" to our dates, as well as "st", "nd", "rd" and "th" by suffixing the day format with "dd" as in our first example. Our first example also shows that we can embed text (such as "of") in our date string,
We not going to rewrite the manual here or give an example of every possible combination of date formats as the Oracle SQL Reference
already does a good job of that.
To learn more about SQL why not take a training course with one of our partners who offer on-demand training on-line as well as traditional classroom-based training. See here for more details.