DATE adalah salah satu tipe dalam dalam Oracle, seperti halnya VARCHAr2 dan NUMBER. Tipe data DATE disimpan oleh Oracle dalam format spesial yang menyimpan tidak hanya bulan, tahun dan tanggal tetapi juga menyimpan jam, menit dan detik. Kita dapat memformat tampilan data bertipe DATE ini sehingga dapat menampilkan tanggal saja atau tanggal dengan jam, atau abad. Kita dapat menggunakan tipe data TIMESTAMP untuk menyimpan bilangan detiknya. SQL*Plus dan SQL mengenali kolom yang bertipe DATE, dan mereka memahami instruksi untuk melakukan operasi aritmatik terhadap data tersebut.
SYSDATE, CURRENT_DATE, SYSTIMESTAMP
Oracle akan mengambil nilai tanggal dan jam di komputer Orcle tersebut terinstal sebagai nilai current date and time. Kita dapat mengambilnya melalui fungsi SYSDATE (SYStem DATE). Fungsi kedua yaitu CURRENT_DATE, akan mengambil nilai tanggal dan waktu berdasarkan time zone tempat komputer Oracle terinstal. Fungsi ketiga, SYSTIMESTAMP, akan mengambil nilai tanggal dan waktu adri komputer tempat Oracle terinstal tetapi ditampilkan dalam format TIMESTAMP.
SELECT SYSDATE FROM dual
SELECT CURRENT_DATE FROM dual
SELECT SYSTIMESTAMP FROM dual
Menghitung perbedaan antara dua tanggal
Seperti saya jelaskan di awal, Oracle dapat melakukan perhitungan aritmatik terhadap data bertipe DATE. Contoh berikut akan memperlihatkan salah satu penggunaan operasi aritmatik, yaitu pengurangan, kita akan mencoba untuk mencari tahu perbedaan tanggal antara nilai dari kolom hire_date dalam kolom employees dengan tanggal sekarang, ketikan perintah berikut:
SELECT hire_date AS Tanggal_Masuk, SYSDATE AS Tanggal_Sekarang, SYSDATE-hire_date AS Beda_Tanggal FROM employees
Jika anda belum memiliki tabel employees, anda dapat mengikuti tutorialnya di sini.
Menambahkan bulan
Misalnya kita ingin mencari tahu tanggal berapa setelah 4 bulan dari sekarang, perintahnya adalah sebagai berikut:
SELECT ADD_MONTHS(SYSDATE, 4) AS Empat_bulan_kemudian FROM dual
Atau misalnya kita ingin melakukan evaluasi terhadap karyawan kita (dari tabel employees), evaluasi ini dilakukan 10 bulan setelah mereka masuk kerja (dari kolom hire_date), maka perintahnya adalah
SELECT hire_date AS Tanggal_masuk, ADD_MONTHS(hire_date, 10) AS Tanggal_evaluasi FROM employees
Mengurangkan bulan
Sama-sama menggunakan fungsi ADD_MONTHS, tetapi dengan memasukan parameter negatif. Misal kita ingin tahu tanggal dari 5 bulan sebelum tanggal sekarang.
SELECT ADD_MONTHS(SYSDATE, -5) FROM dual
Atau misalnya kita ingin melakukan liburan pada tanggal 10 September 2011, pemesanan tempat paling lambat dilakukan 3 bulan sebelum hari H, tanggal berapa kita harus sudah memesan tempat tersebut?
SELECT ADD_MONTHS(TO_DATE(’10-Sep-11′), -3)-1 AS Tanggal_pesan FROM dual
Jawabannya adalah kita paling lambat harus memesan tempat pada tanggal 9 Juni 2011.
GREATES dan LEAST
Masih ingat pembahasan fungsi ini pada tutorial Bermain angka dengan Oracle, di sini fungsinya sama saja, hanya di sini kita terapkan pada data bertipe tanggal. GREATES akan mengembalikan tanggal yang tertua sedangkan LEAST akan mengembalikan tanggal yang termuda.
SELECT GREATEST(TO_DATE(’10-Sep-12′),TO_DATE(’10-Oct-12′)) FROM dual
SELECT LEAST(TO_DATE(’10-Sep-12′),TO_DATE(’10-Oct-12′)) FROM dual
Kalau anda perhatikan, beberapa kali saya menggunakan fungsi TO_DATE di atas, mengapa saya harus menggunakan fungsi ini? Jawabannya adalah karena saya mengoperasikan fungsi-fungsi tanggal ini ke dalam nilai literal, sehingga kita harus terlebih dahulu mengkonversi literal ini ke dalam format tanggal supaya sesuai. Jika data yang kita operasikan berasal dari kolom bertipe DATE, maka konversi dengan TO_DATE tidak kita perlukan (perhatikan contoh pertama penggunaan fungsi ADD_MONTHS). Untuk lebih jelas silahkan coba perintah berikut dan bandingkan hasilnya dengan contoh sebelumnya
SELECT GREATEST(’10-Sep-12′,’10-Oct-12′) FROM dual
SELECT LEAST(’10-Sep-12′,’10-Oct-12′) FROM dual
Bentuk umum fungsi TO_DATE:
TO_DATE(string [,'format'])
Dengan ketidakhadiran fungsi TO_DATE, maka tanggal yang dimasukan akan dianggap sebagai string dan fungsi GREATEST dan LEAST akan memperlakukan tanggal tersebut sebagai string. Beberapa batasan yang dilakukan dalam fungsi TO_DATE:
- Literal tidak boleh berbentuk string, misalnya “saya ganteng”.
- Literal tidak boleh berbentuk ejaan, misalnya “Friday”, harus berbentuk angka.
- Tanda baca diijinkan.
- Format fm tidak diperlukan, jika ada maka akan diabaikan.
- Jika literal mengandung bulan, maka penulisannya harus merupakan ejaan bulan tersebut, misal “sep” jika memakai MON atau “september” jika memakai MONTH
Silahkan coba contoh-contoh berikut supaya lebih memahami:
SELECT TO_DATE(’20-Sep-1988′, ‘DD-MON-YY’) FROM dual
SELECT TO_DATE(’20091988′, ‘DDMMYYYY’) FROM dual
Coba perhatikan contoh berikut:
SELECT TO_DATE(’09-20-88′) FROM dual
Yang tampil adalah error, sebab Oracle tidak mengenali format penulisan tanggal seperti bulan-hari-tahun. Untuk membuatnya dikenali maka kita harus memberitahunya secara eksplisit seperti di bawah ini:
SELECT TO_DATE(’09-20-88′, ‘MM-DD-YY’) FROM dual
NEXT_DAY
Misalnya kita ingin mencari tahu tanggal berapakah hari kamis pertama setelah tanggal 9 Desember 2010, perintahnya sebagai berikut:
SELECT NEXT_DAY(TO_DATE(’09-Dec-10′), ‘Thuesday’) AS Kamis FROM dual
Fungsi NEXT_DAY sama seperti fungsi lebih besar dari (>), dia akan mencari tanggal dari hari yang lebih besar dari tanggal yang ditetapkan.
LAST_DAY
Fungsi ini akan mengembalikan tanggal terakhir dalam bulan yang bersangkutan.
SELECT LAST_DAY(SYSDATE) FROM dual
Mencari perbedaan bulan antara dua tanggal
Misal kita ingin mencari tahu berapa bulan lamanya suatu karyawan bekerja, dihitung dari tanggal hire_date dan tanggal sekarang
SELECT first_name AS Nama, hire_date, MONTHS_BETWEEN(SYSDATE,hire_date) AS Lama_kerja FROM employees
Hasilnya tidak bagus bukan, masih mengandung pecahan. Untuk menghilangkannya kita gunakan saja fungs FLOOR.
SELECT first_name AS Nama, hire_date, FLOOR(MONTHS_BETWEEN(SYSDATE,hire_date)) AS Lama_kerja FROM employees
Kombinasi antara beberapa fungsi
Misalnya kita ingin menaikan gaji kerja karywan, kenaikan gaji baru kita lakukan setelah 6 bulan bekerja, tanggal berapakah gaji karyawan tersebut sudah naik?
SELECT first_name AS Nama, hire_date AS Tanggal_masuk, LAST_DAY(ADD_MONtHS(hire_date, 6))+1 AS Gaji_naik FROM employees
Pertama kita memakai fungsi ADD_MONTHS untuk mencari tahu tanggal setalah 6 bulan, kemudian kita ,menggunakan fungsi LAS_DAY untuk mencari tahu tanggal terakhir di bulan itu, setelah dapat tanggal tersebut ditambahkan 1 untuk mendapatkan tanggal 1 bulan berikutnya.
Jika kita ingin mencari tahu seberapa lama para karyawan harus bekerja sebelum mengalami kenaikan gaji, kita dapat melakukannya dengan menggunakan perintah berikut:
SELECT first_name AS Nama, hire_date AS Tanggal_masuk, (LAST_DAY(ADD_MONtHS(hire_date, 6))+1)-hire_date AS Tunggu FROM employees
Penggunaan ROUND dan TRUNC
Di awal kita sudah melihat bahwa data bertipe tanggal dapat dikenai operasi aritmatik (dicontohkan operasi pengurangan). Tapi kita perhatikan hasilnya mempunyai bilangan pecahan, apa yang terjadi? Ini disebabkan Orcale menyimpan tanggal berikut dengan jam, menit dan detik, sehingga nilai-nilai ini turut diperhitungkan. Untuk mengatasinya kita harus melakukan pembulatan terhadap data tanggal tersebut sebelum dikenai operasi aritmatik. Beberapa asumsi mengenai pembulatan yang dilakukan:
- Tanggal yang dimasukan sebagai literal, contoh ’10-Sep-2010′ diberikan nilai jamnya adalah 00.00 (awal hari).
- Tanggal yang dimasukan melalui SQL*Plus, tanpa diberitahukan secara spesifik formatnya, akan dianggap memiliki nilai jam 00.0.
- SYSDATE akan selalu memiliki komponen tanggal dan waktu. Pembulatan (ROUND) akan dilakukan ke jam 00.00 terdekat. Jika waktu bernilai sebelum 12.00 akan dibulatkan ke jam 00.00, jika sesudah 12.00 akan dibulatkan ke jam 24.00 (00.00 hari berikutnya). Kalau TRUNC akan selalu menset waktu ke jam 00.00 hari yang bersangkutan.
SELECT TO_DATE(’08-Dec-10′)-ROUND(SYSDATE) FROM dual
*perintah ini saya jalankan pada tanggal 9 Desember 2010 jam 20:27
TO_DATE dan TO_CHAR
Fungsi TO_DATE sudah saya bahas sedikit di atas, fungsi TO_CHAR berfungsi kebalikannya, yaitu mengubah tanggal menjadi bertipe string. Bentuk umumnya:
TO_DATE(string [,'format'[,'NLSparameter']])
TO_CHAR(date [,'format'[,'NLSparameter']])
Untuk ‘date’, harus berasal dari kolom yang bertipe date, jika ingin digunakan literal maka harus dibungkus dengan fungsi TO_DATE. Sedangkan string dapat berasal dari kolom yang mengandung string atau angka, literal string atau literal angka. ‘format’ adalah format tanggal, ada banyak sekali format tanggal dalam Oracle, di bawah ini hanya sebagian format yang paling sering digunakan dalam fungsi TO_CHAR dan TO_DATE:
- / , – : . ;
Tanda baca yang akan ditampilkan pada fungsi TO_CHAR, untuk TO_DATE akan diabaikan. - A.D atau AD
Indikator AD, dengan atau tanpa tanda titik. - A.M atau AM
Menampilkan AM atau PM, tergantung nilai waktunya, dengan atau tanpa tanda titik. - B.C atau BC
Sama seperti A.D atau AD. - CC
Nilai abad, misalnya 21 untuk tahun 2010. - D
Angka hari dalam seminggu, bernilai 1-7. - DAY
Nama hari, dalam bahasa Inggris. - DD
Angka hari dalam 1 bukan, bernilai 1-31. - DDD
Angka hari dalam setahun, dihitung sejak 1 Januari, bernilai 1-366. - DL
Tanggal dalam format panjang, untuk standar Amerika berformat ‘fmDay, Month dd, yyyy’. - DS
Tanggal dalam format pendek, untuk standar Amerika berformat ‘MM/DD/RRRR’. - DY
Nama hari disingkat dalam tiga huruf, misal FRI untuk Friday. - FM
Menghilangkan spasi di akhir dan awal sehingga tanggal dan waktu ditampilkan hanya selebar datanya. - HH
Jam dalam satu hari, bernilai 1-12. - MM
Angka bulan dalam satu tahun, bernilai 1-12. - MON
Nama bulan disingkat menjadi tiga huruf,misal Sep untuk September. - MONTH
Nama bulan, dalam bahasa Inggris. - P.M
Sama seperti A.M. - YEAR
Sebutan untuk tahun. - YYYY
Tahun dalam bentuk 4 digit - Y,YYY
Tahun dengan pemisah koma untuk digit pertama. - Y
Digit terakhir dari tahun. - YY
Dua digit terakhir dari tahun. - YYY
Tiga digit terakhir dari tahun.
Format berikut hanya berfungsi untuk TO_CHAR
- TH
Akhiran untuk angka, misal ddTH akan menghasilkan 24th. Besar kecilnya huruf tergantung dari penulisan format tanggalnya. - SP
Akhiran untuk angka yang memaksa angka tersebut dituliskan bunyinya, misal DDSP dapat menghasilkan Three. Besar kecilnya huruf tergantung dari penulisan format tanggalnya. - SPTH
Kombinasi dari SP dan TH. - THSP
Sama seperti SPTH.
Perhatikan contoh-contoh penggunaannya:
SELECT hire_date AS Awal, TO_CHAR(hire_date, ‘DD Month YEAR’) AS Akhir FROM employees
SELECT hire_date AS Awal, TO_CHAR(hire_date, ‘DD-MM-YYYY’) AS Akhir FROM employees
SELECT hire_date AS Awal, TO_CHAR(hire_date, ‘DDspth MONTH YYYY’) AS Akhir FROM employees
SELECT hire_date AS Awal, TO_CHAR(hire_date, ‘fmDDth MONTH YYYY’) AS Akhir FROM employees
SELECT first_name AS Nama, hire_date AS Tanggal_masuk, TO_CHAR(hire_date, ‘”Masuk pada tanggal” DD fmMONTH YYYY’) AS Akhir FROM employees
SELECT first_name AS Nama, hire_date AS Tanggal_masuk, TO_CHAR(hire_date, ‘”Masuk pada tanggal” DD fmMONTH YYYY “pada jam” HH:MI P.M.’) AS Akhir FROM employees
