Home » SQL & PL/SQL » SQL & PL/SQL » Query date range
Query date range [message #237232] Sun, 13 May 2007 11:15 Go to next message
matrik02
Messages: 64
Registered: November 2005
Member
I have problem with SQL and Visual Basic


I use DTPicker to make a query.I have 2 field: start_date field and end_date field.

sql = "SELECT * FROM kerja WHERE start_date >= #" & Format(DTPicker3.Value, "yyyy-MM-dd") & "# and end_date >= #" & Format(DTPicker3.Value, "yyyy-MM-dd") & "# "


I want to find what should I do on date 28/04/2007 before exam on 12/05/2007 from table by using DTPicker. The result show in the listview does not included the date range '27/04/2007 to 29/04/2007'in the result.This is because 28/04/2007 are between this range. How I can include the record between the date range using SQL?

./fa/2450/0/
  • Attachment: query.JPG
    (Size: 84.58KB, Downloaded 1216 times)

[Updated on: Sun, 13 May 2007 11:16]

Report message to a moderator

Re: Query date range [message #237242 is a reply to message #237232] Sun, 13 May 2007 12:30 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't speak VB, but I'm quite sure that this is because you are using strings instead of dates.

This is what you are doing at the moment: string '12...' is positioned BEFORE string '27...':
SQL> select datum from
  2    (select '27/04/2007' datum from dual
  3     union
  4     select '12/05/2007' datum from dual
  5    )
  6  order by datum;

DATUM
----------
12/05/2007
27/04/2007

But, if you do the right thing - convert strings to dates - you'll get the correct result:
SQL> select datum from
  2    (select to_date('27/04/2007', 'dd/mm/yyyy') datum from dual
  3     union
  4     select to_date('12/05/2007', 'dd/mm/yyyy') datum from dual
  5  )
  6  order by datum;

DATUM
--------
27.04.07
12.05.07

Now, how to incorporate the TO_DATE function into VB, I wouldn't know; there must be a way, and I hope you know it.
Re: Query date range [message #237253 is a reply to message #237232] Sun, 13 May 2007 14:12 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
You are generating a code with hard-coded values instead of using bind variables.

In VB you have to use COMMAND object to enable parameter/bind variables usage and to perform

set rs = cmd.execute


HTH.
Michael

[Updated on: Sun, 13 May 2007 14:14]

Report message to a moderator

Re: Query date range [message #237362 is a reply to message #237253] Mon, 14 May 2007 04:32 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
TRY THIS:--

sql = "SELECT * FROM kerja WHERE start_date >= #" & Format(DTPicker3.Value, "yyyy-MM-dd") & "# and end_date <= #" & Format(DTPicker3.Value, "yyyy-MM-dd") & "# "
Re: Query date range [message #237408 is a reply to message #237362] Mon, 14 May 2007 06:59 Go to previous message
matrik02
Messages: 64
Registered: November 2005
Member
Here It what I want. I have include Or in my query

 sql = "SELECT * FROM kerja WHERE start_date >= #" & Format(DTPicker3.Value, "yyyy-MM-dd") & "# and end_date >= #" & Format(DTPicker3.Value, "yyyy-MM-dd") & "# or start_date <= #" & Format(DTPicker3.Value, "yyyy-MM-dd") & "# and  end_date >= #" & Format(DTPicker3.Value) & "# "
Previous Topic: HOW TO UPDATE TABLE WITH JOIN
Next Topic: Procedure Loading in to memory
Goto Forum:
  


Current Time: Thu Dec 08 20:11:44 CST 2016

Total time taken to generate the page: 0.05230 seconds