Archive

Posts Tagged ‘oracle’

Working with Time on Oracle

March 29, 2013 Leave a comment

As I’ve mentioned in my previous post, we were working on a hospital database for our case study, and that includes storage of Appointments (that’s future date + time). I noticed it odd for Oracle that it doesn’t display time, by default. And it doesn’t have a datatype specialized for time. So I found this tweak using SQL, which you might consider helpful as well.

Let’s say my APPOINTMENT table looks like this:

oracle_time_02

ON SQL:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI';

You may need to enter this before every session, because Oracle’s default setting doesn’t include time at all. HH24 means you will be using a 24-hour time setting – that’s less troublesome than having to deal with AM/PM texts, which you need to parse (I think).

INSERT INTO appointment (appt_no, appt_date, confirm_status, cpt_code, patient_no, prov_no, room_no)
VALUES('APT0011', TO_DATE('05-Feb-2013 14:30', 'DD-MON-YYYY HH24:MI'), 'T', '97810', 'P00019', 'PR0008', '102');
SELECT appt_no, TO_CHAR(appt_date, 'DD-MON-YYYY HH24:MI') appt_date, patient_no
FROM appointment;

Notice that I used the TO_DATE() function to store the time data and the TO_CHAR() to retrieve/display it.

RESULT:
oracle_time_01

NOTE: The Time data will be displayed only upon query (via TO_CHAR()), not in the Object Browser. Well, at least we know it’s stored.

Let me know if it worked for you (or not).

Categories: Machine fix Tags: , ,