CODEWITHSUNDEEP
sqloracle-database
NoviceProgrammer
NoviceProgrammer

Reputation: 9

Understanding Oracle's to_date / to_char

So I am doing some exercises for myself and I gave myself in a situation with this instructions: (Im in a hospital using their test database) Displays:

  1. Lname, Fname, Mname, Birth_date, Chief_Complaint, DTTM_ADM (date/time admission)
  2. Then my search would only be between DTTM_ADM of 2008 and 2010
  3. Chief complaint will be inputted manually by the user.

I know how to display the 1st and 2nd instructions that I gave myself, but I am having a hard time doing the second instructions. I don't know much about SQL and its other functionality (Which is why I am doing this, to learn).

I was told by my instructor to do the function to_char to solve my #2 instruction. Since I am just limiting my search between years (In their system it is like 01-JUL-14).

So my question is, how do I do the to_char or to_date in a way i only need to just instruct my search would only be in years and not having a day and month along the limit (Instead of saying the sql 01-JUL-14, it would be just 2014)?

Table:
PATIENT_ADM A,
PATIENT_ADM B
A.DTTM_ADM > 2008
B.DTTM_ADM < 2010

How do I do this properly? (It's kind of hard doing OJT when you only have a limited access to certain website and the net being awfully slow)

Upvotes: 0

Views: 657

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

First find out if DTTM_ADM is of type date or of a character type like varchar2 or char. It should be a date, but as you say "In their system it is like 01-JUL-14", they may store this in a string.

The function to_char works on dates. to_char(DTTM_ADM,'yyyy') would give you the year as a string ('2009' for instance).

If DTTM_ADM happens to be a string type, then to_char is of no use. You would need the string function SUBSTR instead to get the substring holding the year.

Upvotes: 0

ngrashia
ngrashia

Reputation: 9894

You can do this by 2 methods,

  1. You can use TO_CHAR for year comparison as below:

    PATIENT_ADM A, PATIENT_ADM B TO_CHAR(A.DTTM_ADM,'YYYY') > '2008' AND TO_CHAR(B.DTTM_ADM, 'YYYY') < '2010'

    Explanation, To_Char is used to convert the date into a particular string format say Date only, month only, year only, hours only or Date Month Year with hours seconds etc. Refer the documentation link in answer.

  2. You can use EXTRACT option for year comparison as below

    PATIENT_ADM A, PATIENT_ADM B EXTRACT(YEAR FROM A.DTTM_ADM) > 2008 AND EXTRACT(YEAR FROM B.DTTM_ADM) < 2010

    Explanation: Extract is used to extract particular date/time part values from a datefield. This is particularly useful when your DB has large amount of data, since TO_CHAR is slightly costly operation compared to EXTRACT. Refer source document attached.

Upvotes: 1

Charlesliam
Charlesliam

Reputation: 1313

I'll be using extract() on your where clause.

EXTRACT() to extract and return a year, month, day, hour, minute, second, or time zone from the timestamp types or a DATE.

SELECT *
FROM PATIENT_ADM A, PATIENT_ADM
WHERE EXTRACT(YEAR FROM A.DTTM_ADM) > 2008
AND EXTRACT(YEAR FROM B.DTTM_ADM) < 2010;

Upvotes: 0

WW.
WW.

Reputation: 24271

Try a WHERE clause like this:

WHERE A.DTTM_ADM >= TO_DATE('01-JAN-2008', 'DD-MON-YYYY') AND
      A.DTTM_ADM <  TO_DATE('01-JAN-2011', 'DD-MON-YYYY')

Some notes:-

  • I assume that DTTM_ADM is of type DATE
  • An alternate approach would be to use TO_CHAR on DTTM_ADM, but this would make indexes on that column unusable by Oracle
  • The first condition is a greater than or equal to, incase someone is admitted right on midnight on 01-Jan-2008.
  • The second condition is less than, so that if someone is admitted right on midnight on 01-Jan-2011, they are not included in the results

Upvotes: 0

Related Questions