Home > Oracle > Bermain Tanggal dengan Oracle

Bermain Tanggal dengan Oracle

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

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

About these ads
  1. andrihdt
    December 10, 2010 at 2:41 am

    numpang baca tutor nich wat nambah ilmu gan :)

  2. September 26, 2011 at 2:40 pm

    makasih banget buat bagi2 ilmunya

  3. August 14, 2012 at 7:24 pm

    saya mau nanya dong. saya mau bikin format tanggal pake bahasa indonesia.
    misalnya gini. saya ambil tanggal dari sysdate dengan format ‘dd MONTH yyyy’ maka hasilnya ’15 AUGUST 2012′.
    nah, saya mau bikin format tanggal yang sama ‘dd MONTH yyyy’ tapi hasilnya ’15 Agustus 2012′.
    gimana cara bikinya?? mohon bantuanya :D

    terimakasih sebelumnya ^^

    • August 18, 2012 at 3:03 am

      Pake fungsi TO_CHAR gan, tambahin parameter NLS untuk rubah languangenya. Misalnya
      SELECT TO_CHAR(SYSDATE,'MONTH DD YY','NLS_DATE_LANGUAGE=german') "German Date" from dual;
      Indonesia ada nga yah, ane lupa :D.

      • Blezz
        November 11, 2014 at 3:39 am

        SELECT TO_CHAR(SYSDATE,’DD MONTH YYYY’,’NLS_DATE_LANGUAGE=INDONESIAN’) “Tanggal” from dual;

  4. saifudin
    September 10, 2012 at 8:50 am

    mbak ada gak fungsi di oracle menghtung selisih bulan dan tahun pada orcle

    • September 18, 2012 at 6:38 am

      Selisih bulan -> MONTHS_BETWEEN
      Selisih tahun -> MONTHS_BETWEEN/12

  5. ella
    October 16, 2012 at 1:51 am

    boleh numpang baca gak ?

  6. July 16, 2013 at 6:13 am

    permisi pak saya mau tanya?
    kalau saya menemukan soal seperti ini :
    nama tgl_lahir tgl_sekarang hari tahun jam menit detik
    ——– ———- —————- —— ——- —– ——- ——
    bayu ? 29-02-09 ? ? ? ? 9000000

    solusinya gimana pak?
    mohon pencerahannya
    matursuwun

  7. September 6, 2013 at 4:33 am

    maaf gan mau nanya kalo mau nyari selisih jam dari timestamp gmana?

  8. hayat ale-ale
    September 13, 2013 at 2:46 am

    Assalamualaikum Pak……..
    saya mau tanya, jika kita mencari beda hari bagaimana????????
    saya punya query gini gan dapat dari browsing tapi yang beda jam :

    select (
    TO_DATE(’2010-10-28 12:00′, ‘YYYY-MM-DD HH24:MI’)
    – TO_DATE(’2010-10-28 08:15′, ‘YYYY-MM-DD HH24:MI’)
    ) * (24)
    AS BEDA_WAKTU_DALAM_JAM
    from DUAL
    mohon pencerahannya….
    matr suwun…….

  9. wahyu wijaya jati
    October 2, 2013 at 3:34 pm

    mas, kalo mau menampilkan data seperti ini gimana?

    =======================
    | id | januari | februari | maret |
    =======================
    | 1 | 1 | 0 | 2 |
    =======================

    mohon bantuannya para suhu

  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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: