Home > Machine fix > Working with Time on Oracle

Working with Time on Oracle

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:




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.


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: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: