Datetime Parameter Help! 1

[ekta22]

Hi,

I have a datetime field in my table. In my stored procedure, I have created two parameters for my datetime field - one for date and one for time. Results should display all data greater than that date and time entered by the user. How can I seperate the two such that when the user enters the date it should just check the date and when they enter the time it should check both. They can enter just the date or both together. I hope it makes sense.

Thanks,

-E

 

[SantaMufasa]

Ekta,

Since you have a stored procedure, you have total control over the logic there. You have the options to either:

1) Have the user enter the DATE or TIME component, then prior to calling your stored procedure, format them into either DATE (using TO_DATE function) or TIMESTAMP (using TO_TIMESTAMP function), placing the results into your stored-procedure arguments, or

2) Place the user-supplied entries into VARCHAR2 arguments in your stored-procedure call, then do the same "TO_<whatever>" translation within your stored procedure.

How you deal with the TIMESTAMP data once it is in your stored procedure is completely up to you.

Did I effectively answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ekta22]

Dave,

Is this what I need to do? I declare two variables in my stored procedure, one for date and for time.

vSTART_DATE IN DATE,
vSTART_TIME IN DATE

Then I compare these variables to my database field as follows -

(TO_DATE(TABLE.START_DATETIME) >= vSTART_DATE) AND
(TO_TIMESTAMP(TABLE.START_DATETIME) >= vSTART_TIME)

Thanks!

 

[SantaMufasa]

Not quite, Ekta. But for me to answer your question most specifically, could you please answer these questions for me:[ul][li]When you DESCRIBE your table, what is the data description for the column, "START_DATETIME"?[/li][li]Have you already defined the format that end-users use for "vSTART_DATE" and "vSTART_TIME"? If so, what are those exact formats?[/li][/ul]Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ekta22]

Hello Dave,

-The data description for the column for START_DATETIME is DATE.
-Users will enter the parameters online from a PHP page where date will be in MM/DD/YY format and time will be in 24hr format.

Thanks!

 

[SantaMufasa]

...time will be in 24hr format.

Do users enter just the HOUR, or do they also enter MINUTES and SECONDS? If they enter MINUTES and SECONDS, what punctuation (if any) do they use to delimit all the time components?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ekta22]

They enter Hour, Minutes and Seconds in HH:MM:SS format.

 

[SantaMufasa]

Ekta,

Given your clarifications, you can achieve your objectives with this simplification to your code, both in the stored-procedure invocation and in your stored-procedure body:

(Invocation)
<procedure-name> (to_date(<PHP_date>||' '||<PHP_time>,'mm/dd/yy hh24:mi:ss'), <any other arguments>)

(reference in stored procedure)
…WHERE TABLE.START_DATETIME >= <incoming DATE argument name>…

Note 1: In the above code, we combine the PHP-sourced DATE and TIME components into a single DATE expression in the call to the stored procedure. Oracle DATE expressions always contain both a DATE and a TIME component, regardless of whether you actually use the DATE or the TIME explicitly.

Note 2: If "TABLE.START_DATETIME" is already of datatype DATE, then you do not need to do a TO_DATE conversion.

Let us know if this is helpful information.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ekta22]

Thanks Dave. I think I should be good now. Thanks for your help. I have another question regarding a reserved word. Will start a new thread for that. Thanks again!

 

[Welshbird]

Ekta,

It might be nice to award Dave a star for all of his help here.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SantaMufasa]

Thanks, Fee and Ekta !

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]