4

For eg I have a student table with a DOJ(date of joining) column with its type set as DATE now in that I have stored records in dd-mon-yy format.

I have an IN param at runtime with date passed as string and its in dd/mm/yyyy format. How do I compare and fetch results on date?

I want to fetch count of records of students who have DOJ of 25-AUG-92 per my database table student, but I am getting date as varchar in dd/mm/yyyy format in an IN param, kindly please guide.

I have tried multiple options such as trunc, to_date, to_char but, unfortunately nothing seems to work.

Nick
  • 882
  • 2
  • 9
  • 31
Hitesh Joshi
  • 75
  • 1
  • 1
  • 8

3 Answers3

7

I have a student table with a DOJ(date of joining) column with its type set as DATE now in that I have stored records in dd-mon-yy format.

Not quite, the DATE data-type does not have a format; it is stored internally in tables as 7-bytes (year is 2 bytes and month, day, hour, minute and second are 1-byte each). The user interface you are using (i.e. SQL/PLUS, SQL Developer, Toad, etc.) will handle the formatting of a DATE from its binary format to a human readable format. In SQL/Plus (or SQL Developer) this format is based on the NLS_DATE_FORMAT session parameter.

If the DATE is input using only the day, month and year then the time component is (probably) going to be set to 00:00:00 (midnight).

I have an IN param at runtime with date passed as string or say varchar and its in dd/mm/yyyy format. How do I compare and fetch results on date.?

Assuming the time component for you DOJ column is always midnight then:

SELECT COUNT(*)
FROM   students
WHERE  doj = TO_DATE( your_param, 'dd/mm/yyyy' )

If it isn't always midnight then:

SELECT COUNT(*)
FROM   students
WHERE  TRUNC( doj ) = TO_DATE( your_param, 'dd/mm/yyyy' )

or:

SELECT COUNT(*)
FROM   students
WHERE  doj >= TO_DATE( your_param, 'dd/mm/yyyy' )
AND    doj <  TO_DATE( your_param, 'dd/mm/yyyy' ) + INTERVAL '1' DAY
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Great answer.. you might want to add that the `TRUNC(doj)` option would prevent Oracle from using most indexes on `doj`, which is why your last option is often seen in practice, despite being longer and more complicated. – Matthew McPeak Apr 19 '17 at 20:29
1

The below should do what you've described. If not, provide more information on how "nothing seems to work".

-- Get the count of students with DOJ = 25-AUG-1992
SELECT COUNT(1)
FROM STUDENT
WHERE TRUNC(DOJ) = TO_DATE('25/AUG/1992','dd/mon/yyyy');

The above was pulled from this answer. You may want to look at the answer, because if performance is critical to you, there is a different way to write this query which doesn't use trunc, which will allow Oracle to use index on DOJ, if one is present.

Community
  • 1
  • 1
Nick
  • 882
  • 2
  • 9
  • 31
0

Though I am bit late in posting this but I have been able to resolve this.

What I did was I converted both the dates to_char in similar formats and it worked here is my query condition that worked..

TO_CHAR(TO_DATE(C.DOB, 'DD-MON-YY'),'DD-MON-YY')=TO_CHAR(TO_DATE(P_Dob,'DD/MM/YYYY'),'DD-MON-YY'))

Thanks for the support all. :)

Hitesh Joshi
  • 75
  • 1
  • 1
  • 8