How to pass datetime parameter in where clause?

Question

0.00/5 (No votes)

See more:

Hi, i am writing a query to retrieve the records based on the condition. the input parameters are datetime datetype.

My query is

Select * from tblname where fromdate = 'parameter1' and todate = 'parameter2'

Input values are

1. Parameter1 = '26/04/2013 00:00:00'
2. Paramater2 = '27/04/2013 00:00:00'

If i pass the parameters like '26-APR-13' and '27/03/13', i can retrieve the records. But if i try to pass direct input, I am not getting it. I tried by using TO_DATE(Parameter1 , 'DD/MM/yyyy hh24:mi:ss') function. Even then i cannot retrieve it.

Anyone please help me to resolve this?

Posted 26-Apr-13 23:23pm

Member 8112455

Add a Solution

Comments

Mike Meinz 27-Apr-13 8:46am

I like to use a datetime format of yyyy-MM-dd hh:mm:ss because it eliminates the ambiguity of month first vs. day first.

Also, if your database columns fromdate and todate also contain the time, then your comparison could fail if the times are not absolutely exact.

Member 8112455 3-May-13 1:02am

Thank you for your suggestion. I solved it by converting month into "mmm" format.

Bernhard Hiller 2-May-13 3:19am

Which programming language is use (C#, VB, Java, ...) for the program to communicate with the Oracle database? Most of them offer parameterized queries, and you do not need to worry about formatting the input. Or is it some Oracle specific editor?

Member 8112455 3-May-13 1:03am

Thank you for your suggestion. I solved it by converting month into "mmm" format.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nayan Ambaliya · Answer 1 · 2013-05-01T13:20:00

SQL

Hi, 

--Convert any date input to the stored procedure to datetime
--Example:

declare @tempStartDate datetime
declare @tempEndDate datetime

select @tempStartDate = convert(datetime, @InputStartDate)
select @tempEndDate = convert(datetime, @InputEndDate)

--write the query to retrieve records
select * from dbo.TempTable where fromDate >= @tempStartDate and toDate <= @tempEndDate

--of course, your data will be displayed as result based on whatever value records you have (fromDate and toDate with time component or without time component)

Hope this helps.

Happy coding..!!