PL/SQL Calendar table

Posted: 17 May 2007 in Database & SQL Related

Kita tahu kalo disetiap sintak SQL itu bermacem2, termasuk nyang sering kita pake format Tanggal yang bener2 kita pengenin. Okeh berikut formatnya; konsepnya sederhana aja nama kolom yang pengen dikeluarin misalnya namanya adalah “TGL_MASUK”. Lha baru kita bisa bikin tuh kolom yang biasanya keluarannya cuman tgl kayak “01/01/2008”, itu kita bisa bikinin semau/sehendak kita.

Nih kalo liat formatnya ada dibawah nih –>

select to_date(‘&date’,‘YYYYMMDD’)-1 + level as DateRange

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘ddd’) “DAY OF YEAR”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘day’) “DAY”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘dy’) “SHORT DAY”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level-1, ‘d’) “DAY OF WEEK”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘dd’) “DAY OF MONTH”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘ww’) “WEEK”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘mm’) “MONTH”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘month’) “LONG MONTH”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘mon’) “SHORT MONTH”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘q’) “QUARTER”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘yyyy’) “YEAR”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘hh’) “HOUR”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘mi’) “MIN”

, to_char(to_date(‘&date’,‘YYYYMMDD’)-1 + level, ‘ss’) “SEC”

from dual

where (to_date(‘&date’,‘YYYYMMDD’)-1+level) <= trunc(sysdate)

connect by level<=1000

;

 

Selamet nyoba yak…!

Comments
  1. AJIB….. TAk UNDUH ah…

    mahrus:
    Boleh2… kasih masukan yaa….

  2. maryam says:

    nanya, gimana caranya nge-load picture di VB6. databasenya access. itu disimpennya dalam format apa?path ke filenya or dalam bentuk image? thx ya

    mahrus:
    Disimpan pathnya aja bisa juga, file gambar ditaruh di server trus hanya pathnya yang disimpan di DB. Kalo pengen benernya imagenya disimpan dalam database. Keuntungannya agar aplikasi bisa bandel kalo dipake client server.

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