2

I'm experiencing trouble returning a query to return all columns within a table but limited to the DATE-data-type "enroll_date" column containing '30-Jan-07'; the closest solution is with the below query but neither data is displayed nor the entire workbook-just the column-which leads me to believe that this is not just an issue with approach but perhaps a formatting issue as well.

SELECT TO_DATE(enroll_date, 'DD-MM-YY')
FROM student.enrollment
WHERE enroll_date= '30-Jan-07';

Again, I need to display all columns but only rows only specific to the date '30-Jan-07'. I'm sure a nested solution is ideal and somehow the right solution, but unfortunately my chops aren't there yet but I'm working on it! :D

UPDATE

Please see attached screenshot of output. The query/solution should retrieve all columns and rows enclosed within the red-rectangle mark-up-thank you!

enter image description here

Community
  • 1
  • 1
alexanderjsingleton
  • 925
  • 12
  • 24
  • Use [`TO_DATE`](http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm) string with the proper formatter. – PM 77-1 Feb 18 '16 at 00:54
  • 1
    What is the problem you are having? – Gordon Linoff Feb 18 '16 at 00:58
  • 1
    Possible duplicate of [How to extract only date value from date field in Oracle?](http://stackoverflow.com/questions/5542080/how-to-extract-only-date-value-from-date-field-in-oracle) – MT0 Feb 18 '16 at 01:27
  • 1
    If you are using SQL Developer then go to `Tools > Preferences > Database > NLS` and update the `Date Format` to something like `DD-MON-YYYY HH24:MI:SS` to display the time component. If you are in SQL/Plus then you can do `ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';`. This will show you why your dates are not being matched. – MT0 Feb 18 '16 at 01:37

2 Answers2

1

You can specify whichever columns you want in the following query:

SELECT col1, col2, col3, ...
FROM student.enrollment
WHERE TO_CHAR(enroll_date, 'DD-MON-YY') = '30-JAN-07';
Khalid Amin
  • 872
  • 3
  • 12
  • 26
1

One possible problem is that the date column has a time component (this is hidden in SQL). One method is to use trunc():

SELECT e.*
FROM student.enrollment e
WHERE TRUNC(e.enroll_date) = DATE '2007-01-30';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786