Sebenarnya bukan hal yg aneh.. Coba perhatikan sorting yg dilakukan oracle thd data bertipa karakter berikut, walaupun formatnya seperti tanggal tetapi tetap disortir berdasarkan karakter dari nilai terkecil ke nilai terbesar. (Sebelumnya data sudah saya insert ke tabel mytable dg kolom bertipe varchar2) SQL> SELECT * FROM mytable 2 ORDER BY v_date; V_DATE ---------- 01/10/2007 05/10/2007 06/11/2007 08/11/2007 21/10/2007
query1: SQL> SELECT * FROM mytable 2 WHERE v_date BETWEEN '01/10/2007' AND '06/11/2007'; V_DATE ---------- 01/10/2007 05/10/2007 06/11/2007 query2: SQL> SELECT * FROM mytable 2 WHERE v_date BETWEEN '01/10/2007' AND '6/11/2007'; V_DATE ---------- 01/10/2007 05/10/2007 21/10/2007 06/11/2007 08/11/2007 Query kedua mengirim semua hasil karena karakter pertama (angka 6) dari variabel kedua klausa BETWEEN mempunyai nilai yg lebih besar dari karakter pertama data manapun. Saran saya, untuk query yg berupa range data yg diambil dari kolom bertipe date yg dikonversi ke karakter, sebaiknya menggunakan format 'YYYYMMDD' atau 'YYYY/MM/DD' sehingga pengurutan data bertipe karakter juga sama dengan pengurutan data bertipe date. CMIIW, AH Subekti Pranoto wrote: > > Ada yang tahu perbedaannya dan solusi masalah di bawah ini?? > > query saya, > > SELECT COUNT(*) > FROM my_table > WHERE TO_CHAR(date_time, 'DD/MM/YYYY') BETWEEN '01/10/2007' AND > '06/11/2007' > > >> Result : 4152021 > > dan > > SELECT COUNT(*) > FROM my_table > WHERE TO_CHAR(date_time, 'DD/MM/YYYY') BETWEEN '01/10/2007' AND > '6/11/2007' > > >> Result : 4152021 > > menurut saya, tidak ada perbedaan dalam query tersebut. ada yang bisa > menambahkan?? > > Environment: > Oracle : 9i R2 Enterprise Edition dan 10g R2 Enterprise Edition > TOAD : Toad for Oracle 8.6.0.38 > > regard, > subekti > > ----- Original Message ----- > From: Harry Christian > To: [EMAIL PROTECTED] <mailto:harry85%40gmail.com> > Sent: Monday, November 05, 2007 2:20 PM > Subject: (newbie_hacker) Query Oracle aneh (bugs atau apa ya ?) > > Kemarin saya sudah berkeluh kesah di blog saya > http://harry-christian.blogspot.com/2007/11/bugs-in-oracle-query.html > <http://harry-christian.blogspot.com/2007/11/bugs-in-oracle-query.html> > saya paste aja ya > > I want to query a data with date condition. Guess what ? This similiar > query > give a different result. > > 1st query give the exact result that i wanted. > SELECT * FROM my_table > WHERE TO_CHAR(date_time, 'DD/MM/YYYY') BETWEEN '01/10/2007' and > '6/11/2007' > > 2nd query result nothing > SELECT * FROM my_table > WHERE TO_CHAR(date_time, 'DD/MM/YYYY') BETWEEN '01/10/2007' and > '06/11/2007' > > The different is, the 1st query use 6-11-2007 instead of 06/11/2007. > > FYI, i use Oracle database 9i and running query in Toad for Oracle 8.5.3.2 > > Any idea why this can happen ? > > Ada rekan2 yg tau ga kenapa bisa begitu ? > > -- > ============================== > Thanks & Best Regards > > Harry Christian > > YM : callme_harry > G Talk : [EMAIL PROTECTED] <mailto:harry85%40gmail.com> > MSN : [EMAIL PROTECTED] <mailto:h4rry_12%40hotmail.com> > Skype : harry_christian > Friendster : > http://www.friendster.com/addfriendrequest.php?uid=5590705 > <http://www.friendster.com/addfriendrequest.php?uid=5590705> > Blog : http://www.harry-christian.blogspot.com > <http://www.harry-christian.blogspot.com> > =============================== > > [ > > Recent Activity > > * > 9 > New Members > > <http://groups.yahoo.com/group/indo-oracle/members;_ylc=X3oDMTJmc2ViaGxjBF9TAzk3MzU5NzE0BGdycElkAzMyMTMyMjUEZ3Jwc3BJZAMxNzA1MDA3MTgzBHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzExOTQyNTc2OTY-> > > Visit Your Group > <http://groups.yahoo.com/group/indo-oracle;_ylc=X3oDMTJldDcyOGlpBF9TAzk3MzU5NzE0BGdycElkAzMyMTMyMjUEZ3Jwc3BJZAMxNzA1MDA3MTgzBHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTE5NDI1NzY5Ng--> > > > Yahoo! Finance > > It's Now Personal > <http://us.ard.yahoo.com/SIG=12j1ktknv/M=493064.10729649.11333340.8674578/D=groups/S=1705007183:NC/Y=YAHOO/EXP=1194264896/A=4507179/R=0/SIG=12de4rskk/*http://us.rd.yahoo.com/evt=50284/*http://finance.yahoo.com/personal-finance> > > Guides, news, > > advice & more. > > New business? > > Get new customers. > <http://us.ard.yahoo.com/SIG=12jbappck/M=493064.10729656.11333347.8674578/D=groups/S=1705007183:NC/Y=YAHOO/EXP=1194264896/A=3848640/R=0/SIG=131an6mds/*http://searchmarketing.yahoo.com/arp/srchv2.php?o=US2002&cmp=Yahoo&ctv=Groups1&s=Y&s2=&s3=&b=50> > > List your web site > > in Yahoo! Search. > > Yahoo! Groups > > Dog Zone > <http://us.ard.yahoo.com/SIG=12jlni9j1/M=493064.11292562.11840176.8674578/D=groups/S=1705007183:NC/Y=YAHOO/EXP=1194264896/A=4836041/R=0/SIG=11o19ppl5/*http://advision.webevents.yahoo.com/dogzone/index.html> > > Connect w/others > > who love dogs. > > . > >
