Help with oracle select statement syntax 1

[chilly442]

I have a user selected start time and a user selected end time defined as follows:

Public STime As Date = Format(MyDate, "MM/dd/yyyy")
Public ETime As Date = Format(MyDate, "MM/dd/yyyy")
Public MyDate As Date

So the user selects dates from calendars, and the Unit comes from a dropdown list.
I need to base a select statement on the selections made by the user.

"SELECT * FROM Events WHERE UnitNumber = '" & Unit.Text & "'
AND StartTime <= TO_DATE('" & STime & "', 'MM/DD/YYYY')
AND EndTime >= TO_DATE('" & ETime & "', 'MM/DD/YYYY')"

This returns all records for the unit selected, not the records with dates between the start time and end time.
Am I using the TO_DATE function properly?
Do I even need to use it since STime and ETime are already dates?

Any suggestions for changes?
Help gets you stars!



Thanks,
Chilly442

 

[HarleyQuinn]

You are currently checking for dates before the start date and after the end date, try:

"SELECT * FROM Events WHERE UnitNumber = '" & Unit.Text & "'
AND StartTime [red]>[/red]= TO_DATE('" & STime & "', 'MM/DD/YYYY')
AND EndTime [red]<[/red]= TO_DATE('" & ETime & "', 'MM/DD/YYYY')"

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[chilly442]

This is returning all records that have the month between STime and ETime but the years are outside the range.

"SELECT * FROM Events WHERE UnitNumber = '" & Unit.Text & "'
AND StartTime >= TO_DATE('" & STime & "', 'MM/DD/YYYY')
AND EndTime <= TO_DATE('" & ETime & "', 'MM/DD/YYYY')"

STime = 01/01/2009
ETime = 02/01/2009

But I get 2006, 2007, 2008 and 2009

Any ideas on what the problem is?
I have a Try and Catch statement that is not giving any errors.
I played with this statement all day yesterday, and now this morning with no luck. Nothing seems to give me what I need.

I need all records that are equal to or between STime and ETime for the selected Unit regardless of the time.

Let me know if I need to post more information to help with my problem.

Thanks,
Chilly442

 

[HarleyQuinn]

It won't fire the Try...Catch block because there is no error, I'd put my money on it being a problem with the way the dates are being queried in Oracle.

I don't have access to an Oracle instance at work to test this theory but just to see if we can get you a bit further along, what happens if you try:

"SELECT * FROM Events WHERE UnitNumber = '" & Unit.Text & "'
AND TO_DATE(StartTime, 'MM/DD/YYYY') >= TO_DATE('" & STime & "', 'MM/DD/YYYY')
AND TO_DATE(EndTime, 'MM/DD/YYYY') <= TO_DATE('" & ETime & "', 'MM/DD/YYYY')"

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[chilly442]

Running the code above I get the error:

"Date format picture ends before converting entire input string"

???

Thanks,
Chilly442

 

[chilly442]

Breakpoint shows:
STime = 01/01/2009
ETime = 02/01/2009

After TO_DATE:
STime = 1/1/2009
ETime = 2/1/2009

Stored in DB as:
01/01/2009
02/01/2009

This might be the problem.
What do I need to do to get them both to match?

Thanks,
Chilly442

 

[HarleyQuinn]

Have you tried not using TO_DATE at all?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[chilly442]

I am not sure what the statement should look like with or without it. I'll give it a shot and see if it will work without.

Thanks for the time. It sure helps.



Thanks,
Chilly442

 

[HarleyQuinn]

It might be worth finding out (for us to help) what type of field StartTime is and what the format of the data contained in it is.

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[chilly442]

I deleted the table, rebuilt it, then repopulated it.

I changed the select statement and now I am getting what I need. Guess that the format in the table might have been the problem all along.

So here is what I have now.

"SELECT * FROM Events WHERE Unit = '" & Unit.Text & "' AND StartTime >= TO_DATE('" & STime & "', 'MM/DD/YYYY') AND EndTime <= TO_DATE('" & ETime & "', 'MM/DD/YYYY')"

Thank you for taking the time to help out. I am not very good at writing statements for Oracle yet.

Thanks,
Chilly442